Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Run a macro from command button Results

I have a spreadsheet containing a list of products for reference purposes.
This list will often be utilized by searching for data in one column to see
if it exists and in many cases there will be multiple matches.

I can do this by using a Custom auto filter using the "contains" operator.
In an effort to not make people go through the process of clicking the filter
arrow, selecting "custom", etc. ... I was hoping I could just set up a cell
where a macro would grab that value and use it for the search criteria - that
way they could type the search keywords and hit a button for results.

I've found though that although while recording the macro I can copy and
paste the value from a cell into the filter area by using shortcut keys, the
macro script shows it as a static value, not a paste command. Therefore each
time I run the macro using different keywords in the cell, it doesn't paste
the value of that cell in the filter area, it only just puts down the value
that was input when I created the macro. Cell references don't seem to be
available to use there either. I know this process would be easier in
Access, but I would prefer to keep this in Excel.

Any ideas? Thanks.

Hi all,

I'm a bit puzzled. I wrote an app for some users (which runs just fine and dandy on the box I developed it on), but it will not execute certain code on other boxes.

Their security settings are set to Low and the excel file itself loads just fine. But when they hit certain command buttons within the app, I get a windows error "Security Settings do not allow this macro to run."

Is this a Windows setting thing (we run Windows 2000 w/ service pack 4 if that matters) where windows itself has been set to block macros from running, or is this an excel thing that I just don't understand?

And again, the app runs just fine on the box I wrote it on. It's just everywhere else that has this issue (rather embarassing when I was demoing it....)

Help.
I am trying to do the Ttest in the data analysis pak. I have a command
button that calls the following macro.

Sub Ttest()
'
' Ttest Macro
'
AddIns("Analysis ToolPak").Installed = True
AddIns("Analysis ToolPak - VBA").Installed = True
Application.Run "ATPVBAEN.XLA!Pttestm",
ActiveSheet.Range("$D$52:$D$72"), ActiveSheet.Range("$E$52:$E$72"), "Ttest",
True, 0.05, 0
Cells.Select
Selection.Columns.AutoFit
Range("D3").Select
End Sub

When it executes it generates an error report to send to Microsoft.
I have the data analysis add in's checkmarked; and in references the
ATPVBAEN is checkmarked as well.
When I try and record the same macro over, I select from the Tool bar -
Tools/Data analysis/Ttest/ttest-two sample assuming unequal. The next screen
comes up, but if you select the grid from this menu so you can select the
range, and click in the cell you want, the select dots are over two cells. I
have tried this on another PC with the same loads of excel (latest 2003 with
sp1) it works fine.
So, I am at a loss as to how to fix this error, or is it the PC and will
have to rebuild.
I have deinstalled Office 2003 and reinstalled with the latest upgrades.

Thanks in advance for any help

--
mlowe

Normally start Excel via the Window's-Run command box.

Approx 1/day, use a simple form-creation macro from Personal.xls via a
custom button which I would like to call from the command line like Word
does. (IE: windows-R excel /mMACROenter)

However Help/Google imply Excel's equivalents to Word's /mMACRONAME switch
are auto_open or workbook _open, neither of which apply in this instance
since the macro is part of Personal.xls and shouldn't run everytime Excel
opens. (BTW, using Office97)

Are the solutions really:
1. leaving well enough alone
2. saving the results to a file and cluttering the desktop with yet
another icon
3. recoding in Word
???

Hi!

I'm trying to run an Excel Addin which is password protected and therefore I
cannot have it's code.

I'm able to run the main procedure from Excel, by:
Application.Run "Main_Procedure_Name"
(I know the main procedure name)

The problem is that if I try to run this code from Word, by defining an
Excel object (named XLApp), and running the following command:
XLApp.Run "Main_Procedure_Name", It cannot find the procedure.

It's weird because problem is solved if I move the Addin control to the main
command bar, and runs the following command instead of XLAPP.Run
"Main_Procedure_Name":

XLApp.CommandBars("Worksheet Menu
Bar").Controls("MyAddinControlCaption").Execute.
(Replacing "MyAddinControlCaption" with the real caption of the addin
button.)

Therefore it seems that it's not a problem with my Excel object definition,
or something, but I have no explanation why it can find the procedure when
it's executed when Excel is opened regularely (not by Word macro), but it
can't find it (though able to activate it using a control) when it's
executed by Word Macro.

Macro works fine using the solution of moving the Addin control to the main
control bar and using the XLApp.CommandBars.....Execute command, but I don't
want to move the Addin control to the main command bar in each of the
computers I wish to install the macro on..

Kind Regards,
Amir.

I have a faily basic set of sorting macros that run from command
buttons in Excel. Each time it involves changing sheets and sorting
one or 2 columns.
When i run this in Excel everything works fine all code works as
expected.
When i place this file in a central area for other users to access it,
it is opened in IE. The problem occurs when clicking on any of the
command buttons. In the background the correct sorting is done again,
but i need to manually highlight the cells on the sheet to see the
results of the search each time.
How can i force the excel file to refresh the IE screen to show the
results hidden behind?

thanks
Innes

I have the following Macro (which I need to run on approximately 200
different workbooks) -- what is the BEST way to achieve this? Should I use
a Function (and if so, what would it look like)?

NOTE: I'm assuming that this Macro should reside in a Module -- but, should
this be run outside of EXCEL (say, from a Form in ACCESS)?

Here's my code:

Does anyone see anything that I may be missing? Here's the MOST important
thing I need this code to do (assuming there are no further modifications
needed) ...

This code (Macro) needs to perform this same task on approximately 200
different Workbooks (all residing in the same Network Directory), but ONLY
when a Command Button is pressed. I'm assuming that I'll need to create a
Form (in ACCESS perhaps) and call this Macro -- correct? How would I do
this, and how would I get this Macro to perform this Link Update on all 200
or so Workbooks?

Private Sub Workbook_Open()
Dim vLinkSources
Dim iLinkSource As Integer
Dim AnySheet As Worksheet
For Each AnySheet In ActiveWorkbook.Worksheets
ActiveWorkbook.Worksheets(AnySheet.Name).Unprotect
Password:="mypassword"
Next
vLinkSources = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(vLinkSources) Then
For iLinkSource = LBound(vLinkSources) To
UBound(vLinkSources)
ActiveWorkbook.UpdateLink vLinkSources(iLinkSource), xlExcelLinks
Next
End If
For Each AnySheet In ActiveWorkbook.Worksheets
ActiveWorkbook.Worksheets(AnySheet.Name).Protect
Password:="mypassword"
Next
End Sub

I know why I'm getting the error.....cos my macro begins with:

sub macroname(HWflag as integer)

if HWflag = 1 then goto 100
I need the variable passed from another macro for reasons that don't matter but I also need to be able to run this macro from a button. I get the error when running it from a button, obviously because the HWflag variable doesnt exist at that point but does when this macro is run from another macro. I've tested running this from a button without the (HWflag as integer) and it works fine so I'm positive what the error is, I just don't know how to cure it.

What I need to know is how to pass a variable (of 0 value) to this macro from a command button click.

I'm sure this is a bonehead question from a noob though.

Hi

I have a VBA macro in a workbook which is invoked by clicking on a command button in a worksheet.

It is intended that the macro only ever be invoked once in the workbook. (Basically, the user puts some initialising data into a separate dedicated worksheet in the template workbook, runs the macro which does various things one-off with the initialising data, then deletes the worksheet containing that data.)

The macro already contains code which prevents its execution twice, but to tidy up the workbook I would also quite like it if the command button used to invoke it is also deleted from the workbook at the conclusion of the first execution of the macro. Can someone please give me guidance on that instruction?

Thanks.

Hi,
same question posted here http://www.mrexcel.com/forum/showthread.php?t=338495

Hi,

I have about 500 sheets in excel. each sheet is named with the managers name. John, Jim, Etc, each sheet has to be emailed as an attachment to the respective manager, That is sheet John has to be email to John@gmail.com, Sheet Jim has to be emailed to Jim@yahoo.com etc.,

I have all the sheet names and their email address in one master sheet.
So some kind of vlookup has to be done between the sheet names and the email address.

When I run a macro 500 emails has to be sent according to the sheet names.
or
A userform will help me.
there should be 2 list boxes one should take all the sheet names from mastersheet, and another list box should take all the email addresses.
So I can select manually the sheet which i wanted to email as an attachment and the email address.

example:
list box one will have, SheetJohn, SheetJim, SheetMercy etc., etc(Data from mastersheet Column A)
List box two will have, Jim@gmail.com, John@yahoo.com, Mercy@hotmail.com etc( data from Mastersheet Column B)
and there should be one command button send mail.
If I select SheetJohn from list box 1 & Select
John@yahoo.com in list box 2 and then I click Send, only that sheet has to be sent as an attachment to that email address.

the subject is one line : "Outstandnig Invoice"
The body of the message is : Hi,

"Test test test test test test "

I dont know if this is possible... But Appreciate your helps.

Hello,

I am trying to create a toolbar button, paste an icon on it, and assign
it a macro -- all at run time.

Here is what I tried.
Insert->Picture->From File: ~/Desktop/whatever.bmp
Tools->Macro->Record New Macro: OK.
Selected the foo.bmp image with the mouse
Hit Ctrl-c to copy the image to the clipboard
On the toolbar, right-clicked, ->Customize
In the Categories field, selected Macros
From the Commands field, dragged the Custom Button with smiley to the
toolbar
Selected the new button on the toolbar
Clicked Modify Selection
Changed the name to "&Show All Names"
Clicked Paste Button Image
Clicked Assign A Macro: Selected my Show_All_Names macro
Tools->Macro->Stop Recording

Here is the code it recorded, without comments:

Sub Macro1()
ActiveSheet.Shapes("Picture 1").Select
Selection.Copy
Application.CommandBars("Standard").Controls.Add Type:=msoControlButton,
ID _
:=2950, Before:=31
End Sub

If I run Macro1() again, a new button is created, but no macro is assigned,
and the button shows a smiley face, not whatever.bmp. Clearly those steps
weren't recorded. And though I've searched through the Object Browser, I
haven't found a way to do it.

Can anyone out there think of a way to do this? Can I programmatically
manipulate the drop-down menus? If I have to manipulate keystrokes, I will.

Thanks in advance,
Gregory Kip

Sorry about the false email address. I dislike spam. Please respond to the
newsgroup.

I've put macro in module, then make a custom button on the toolbar to invoke
a macro.(according to excel help):

--------------------------------------
Create a toolbar button that runs your macro

1. On the Tools menu, click Customize, and then click the
Commands tab.

2. Under Categories, click Macros.

3. Drag the custom button to the toolbar where you want it.

4. On the Customize dialog box, click Modify Selection,
and then click Assign Macro.

5. In the Assign Macro dialog box, click the name of your
macro, and then click OK.

6. To change the appearance of the button, click Modify
Selection again, point to Change Button Image, and click one of the
available images; or click Edit Button Image and use the Button Editor to
create your own image.

7. Click Close.
-----------------------------------------------------

But something was wrong when I've assigned macro, and I find that this
custom Toolbar button appers in all my Excel documents.

When I click on it, it show that workbook contains macros, though there was
no macro earlier. I find that this button attach "Module1" macro to Excel
file when I click on it. So, all Excel document now have this nasty button,
that insert macro in workbook.

How to remove this Toolbar Button from all Excel documents? How to remove
this unwanted macro from workbooks where this module has been already
inserted?

Thanks

I've been getting fatal errors in XL2000 a lot lately. I looked around
here to see if I could find some info on the problem. I found a thread
saying that "controls" can cause XL lockups. Does this mean macro
buttons? In my case, macro buttons are just buttons I create from the
"Forms" toolbar, rename and assign a macro to. Do they cause XL
lockups?
I downloaded a program called "CodeCleaner" but it didn't install,
apparently. When I tried to run it per the instructions at the end of
the install, the command to run it doesn't appear on my "Tools,
Options" menu as it's supposed to. It's there and checked in the
"Add-ins" menu, but not anywhere I can access it. Tried from both the
spreadsheet and the VBE editor.
Is this the most likely source of my problem? If so, what can I do
about it? If not, what is the most likely source and what can you
reccommend about that?
BTW, I don't think it's memory related. I have 1G of memory. I do,
however, run a lot of stuff normally, including the 2M XL file I'm
usually working on and maybe one or two others, Novell GroupWise, my
browser, and often a file folder or 2 and possibly a Word documemt. I
added it all up from the Task Manager and show just over 200M.
Thanks in advance.

I have a macro that saves the template as an xls. file based upon cell
contents. The location is also based upon another cell contents. The macro
works correctly....once. After that, the template utilizes the last
location|filename that the macro used as the current location and filename in
the macro button.
The macro looks like this:
sPath = Range("B8").Value
If Right(sPath, 1) < "" Then sPath = sPath & ""
ActiveWorkbook.SaveAs sPath & ActiveSheet.Range("B9").Value
I am attempting to reset the macro prior to it running by using the "assign
macro" and "macro name" on the toolbar(macro button). I think I need to use
"MsoCommandBarPopup." command to accomplish this, but I can't get it to
work!( or maybe find a way to keep this from happening!) I am new to visual
basic and learning fast..... maybe not fast enough!
Thanks for your help.
Tim

This is the code I am using to send an excel worksheet as an attachment. The only thing I need to complete this code is to be able to send the contents of a specific word document as the body of the e-mail. Is this possible?

Sub
Mail_ActiveSheet()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook
2007, Outlook 2010
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim OutApp As Object
    Dim OutMail As Object
    
        
    Dim TD As String 'To date
    TD = Range("K30").Value
    
    UnProtectSheet
    
    Range("A1:C1").Select
    Range("C1").Activate
    Selection.EntireColumn.Hidden = True
    
    
    'ActiveSheet.Range("$C$2:$K$385").AutoFilter Field:=1, Criteria1:="<>"
    'Range("D1:K385").Select
    
    'this code will send e-mail to only those marked "active"
    
    Dim strto As String
    For Each cell In ThisWorkbook.Sheets("EmpInfo").Range("H15:H200")
        If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 19).Value) = "yes" Then
            strto = strto & cell.Value & ";"
        End If
    Next cell
    If Len(strto) > 0 Then strto = Left(strto, Len(strto) - 1)

    'code ends here

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sourcewb = ActiveWorkbook

    ' Next, copy the sheet to a new workbook.
    ' You can also use the following line, instead of using the ActiveSheet object,
   ' if you know the name of the sheet you want to mail :
    ' Sheets("Sheet5").Copy
    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook
    
    'code to delete the command buttons
    
    Dim oOle As OLEObject
    For Each oOle In ActiveSheet.OLEObjects
    If TypeName(oOle.Object) = "CommandButton" Then oOle.Delete
    Next oOle

    ' Determine the Excel version, and file extension and format.
    With Destwb
        If Val(Application.Version) < 12 Then
            ' For Excel 2000-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            ' For Excel 2007-2010, exit the subroutine if you answer
            ' NO in the security dialog that is displayed when you copy
            ' a sheet from an .xlsm file with macros disabled.
            If Sourcewb.Name = .Name Then
                With Application
                    .ScreenUpdating = True
                    .EnableEvents = True
                End With
                MsgBox "You answered NO in the security dialog."
                Exit Sub
            Else
                Select Case Sourcewb.FileFormat
                Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56: FileExtStr = ".xls": FileFormatNum = 56
                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
                End Select
            End If
        End If
    End With

    ' You can use the following statements to change all cells in the
   ' worksheet to values.
        With Destwb.Sheets(1).UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
           .Cells(1).Select
        End With
        Application.CutCopyMode = False

    ' Save the new workbook, mail, and then delete it.
    TempFilePath = Environ$("temp") & ""
    TempFileName = "Part of " & Sourcewb.Name & " " _
                 & Format(Now, "dd-mmm-yy h-mm-ss")
                 
    

    Set OutApp = CreateObject("Outlook.Application")
    
    Set OutMail = OutApp.CreateItem(0)

    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, _
                FileFormat:=FileFormatNum
        On Error Resume Next
       ' Change the mail address and subject in the macro before
       ' running the procedure.
        With OutMail
            .To = ""
            .CC = ""
            .BCC = "mye-mailaddress@w/e.com"
            .Subject = "Employee Schedule w/e " & TD
            .Body = "TEST"
            .Attachments.Add Destwb.FullName
            ' You can add other files by uncommenting the following statement.
            '.Attachments.Add ("C:test.txt")
            ' In place of the following statement, you can use ".Display" to
            ' display the mail.
            '.Send
            .Display
            
            
            
        End With
        On Error GoTo 0
        .Close SaveChanges:=False
    End With

    ' Delete the file after sending.
    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub


Hi....I would like to protect some areas of a shared worksheet which has command buttons linked to macros. The macros are used mainly for sorting and its these that im having problems with. When I protect the shared workbook it wont run a sort macro from a command button. I have tried adding a line to the macro unprotecting the sheet and then another line protecting it again but I get "unprotect method of worksheet failed" error message. My ability with VB is very limited and Im using Excel 2007. Hope somebody can help with this.

Many thanks and regards.

I have a VBA application written in Excel 2000.

Running the appl on Excel 2003 generated errors,
and the appl would not run on the Excel 2003 PC.

(I can't remember exactly what the errors read,
but Excel 2003 didn't recognize some components
or code. And the code would not compile.)

The appl contains about 10 forms, custom menus,
macros, & custom functions. The forms have
standard, native controls that include simple text
boxes, command buttons, spin buttons, combo
boxes, & labels. All controls are on forms -- no
controls are imbedded in worksheets.

I can run an earlier version of the same Excel 2000
VBA appl on the same Excel 2003 PC with no
problems. But this earlier version has no forms and
no custom menus, just macros & custom functions.

I suspect the forms are causing the errors, and
the problem is the Excel 2003 object libraries are
different from the Excel 2000 object libraries. And,
I'll need to set references in Excel 2003 to the
Excel 2000 object libraries.

These are the libraries I suspect that I have to set
references for:
- Microsoft Office 9.0 Object Library (MSO9.DLL)
- Microsoft Excel 9.0 Object Library (Excel9.OLB)

(If they are even listed in Excel 2003. I didn't look,
and the PC is at a different location. I'll be trying
to get the appl working on Wed, Aug 9. But, I'll be
at that other location tonight, Tue, Aug 8th, & will
have some brief time on that Excel 2003 PC.)

Am I barking up the right tree,
or just howling at the moon?

Any confirmation, hints, traps, or other possibilities
would be appreciated.

Also, these are the object libraries that are referenced
in Excel 2000 where the appl was developed:
- Visual Basic For Applications (VBE6.DLL)
- Microsoft Excel 9.0 Object Library (Excel9.OLB)
- OLE Automation (stdole2.tlb)
- Microsoft Forms 2.0 Object Library (FM20.DLL)
- Microsoft Office 9.0 Object Library (MSO9.DLL)

Dynamically ReNaming the Most Recent Added Sheet with a Cell Value in the Main Sheet

I have a Template which pulls data from several Sheets and I need to make a Copy of the Main Sheet which has Dynamic data which keeps on changing with the help of DropDown.

Now I have managed to Record a Macro which populates the Entire Sheet contents of the Main Sheet as Values with exactly the same format..

In my Recorded MAcro the first Step is Adding the Sheet and then Copying the ENtire Format of the Main Sheet and some Individual Cells as they are some cells which are Merged and carefully copying these cells Individually..

Now I have managed to make this work decently, however thought of Renaming the Sheet Dynamically with a cell Value but the problem I am facing is I dont know what No of the "Sheet X" is going to get generated and therefore require help in this aspect where I dont know how to keep a Track of the New Generated Sheet..

Please find the earlier query where I had asked about Dynamically Adding Sheet:

http://www.excelforum.com/excel-prog...namically.html

However, In the above query as I was explicitly putting the Sheet No and the Name but in this case I already have the Name Stored in one of the cells and therefore this is a slightly different requirement..

The format of the Sheet is as shown in the attached WorkSheet, strangely when I Run the Macro it works if I am continuosly from Sheet1 and so on , however if I delete any SHeets then it causes the DEbug Error for the Sheet No and does not work smoothly.

I want a Command Button to be Added and on the Click Event of the Command Button I want the SHeet Replication and also Renaming the Sheet with the value conatained in the cell C4 of the Ledger Book concatenated with the text "Ledger Book".

Warm Regards
e4excel

I've got a macro that I run from a button in my Cal_file_processor.xls spreadsheet. It prompts the user to select workbooks using "CalFiles = Application.GetOpenFilename" which opens an Open File Dialogue window with multiselect enabled, then later I refer to the individual files as "Calfiles(counter)" with the counter counting up through all the files in the array as each one is opened and formatted by a called function.

I would like to automate this macro so that I can just run it as a scheduled task with no user input. The files being processed will always be in the same folder, although the file names and quantities will change, and it should run the formatting macro on every file in that folder. The question is, is there a simple plug and play substitute for GetOpenFilename that will automatically pull in an array of files names from a specific folder?

Sub Cal_file_Coverter()

Dim CalTemplate, CalSheet
Dim MywSheet As Worksheet
Dim Sht, counter, StatusCell, MacroPath, IdCounter
' set the array to a variable
Dim CalFiles As Variant
    
    'Opens multiple files loops through each file
   'True is for multi-select
   StatusCell = Range("E2").Address
   IdCounter = 0
    Range("D2:G300").ClearContents
   CalFiles = Application.GetOpenFilename(FileFilter:="microsoft excel files (*.xls), *.xls", Title:="Select
Cal Files to Extract Cal Sheets from", MultiSelect:=True)
           counter = 1
       'check to see if cancel selected in the box, which cause this to be an  error
     On Error Resume Next
     If CalFiles(1) = "" Then
         MsgBox "No Files Selected"
         End
         
      End If
       'turn off error checking
      On Error GoTo 0
     
 ' ubound determines how many items in the array
     While counter <= UBound(CalFiles)
     'file #_ of _ files status display on Cal Processor spreadsheet
     Workbooks("Cal_file_processor.xls").Sheets("Sheet1").Range("A9") = ("Processing
") & counter & (" of ") & UBound(CalFiles) & (" files")
           Workbooks.Open Filename:= _
            CalFiles(counter), ReadOnly:=True
            ActiveWorkbook.Sheets(1).Activate
            'Display current file name in Cal Processor spreadsheet
            StatusCell = Range("D3").Offset(IdCounter, 0).Address
            Workbooks("Cal_file_processor.xls").Sheets("Sheet1").Range(StatusCell) = ActiveWorkbook.Name
            'loops through sheets
       For Each MywSheet In ActiveWorkbook.Worksheets
            ActiveWorkbook.Sheets(MywSheet.Name).Activate
          Call CalSheetExtractor(StatusCell, IdCounter)
            'Debug.Print MywSheet.Name
       Next MywSheet
        Application.DisplayAlerts = False
        ActiveWindow.Close
        Application.DisplayAlerts = True
        
         'increment counter
         counter = counter + 1
     Wend
        Workbooks("Cal_file_processor.xls").Sheets("Sheet1").Range("A9") = _
        ("   Finished Processing ") & counter - 1 & (" of ") & UBound(CalFiles) & ("
files")
        Workbooks("Cal_file_processor.xls").Sheets("Sheet1").Range("A9").Interior.ColorIndex =
4
        
   End Sub
  
 Sub CalSheetExtractor(StatusCell, IdCounter)
  'insert all manner of formatting irrelevant to my question, here
  End Sub


Hello
I need some help from you guys, it'll prob take one of you a few seconds to figure it out! I'm been losing my head over an issue.

I have 20 worksheets, with data from range A10:R59 on each sheet. Basically, the sales ppl report their top 50 sales points.

So it makes 1000 data points in total.

Basically, have a master sheet called "Top 50" and it's suppose to take the top 50 results cumulative all reps. How can I go abouts doing this?

I need some help; also, I have managed to scrap together a macro but im not storng with vba by any means, so this is one method:

flatten copy/paste
Sub HTH()
    Dim i As Long
    Dim LastRow As Long
    On Error Resume Next
     'Turn off screen updating and xlcalculation to  speed up macro
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
         'cycle through  sheets and  copy to sheetA
        For Each ws In ActiveWorkbook.Worksheets
            If Not ws.Name = "Data" And Not ws.Name = "Lookup Values" And Not ws.Name = "District
top 50" Then
                LastRow = Worksheets("Data").Cells.Find(What:="*" _
                , After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                ws.UsedRange.Copy Worksheets("Data").Range("A" & LastRow + 1)
            End If
        Next ws
        LastRow = Worksheets("Data").Cells.Find(What:="*" _
        , After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
         'delete blank rows
        For i = LastRow + 1 To 1 Step -1
            If WorksheetFunction.CountA(Rows(i)) = 0 Then
                Rows(i).EntireRow.Delete
            End If
        Next i
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub
How can I make the above code do the following:
1) select range A10:R59 from each sheet
2) create a button or command that if I place a button on my "top 50", it runs this macro and only gives the top by column "i" which is revenue?

The macro dumps the data on a sheet called "Data" but then i need to be able to have a button on my "top 50" sheet to sort by revenue or sales phase or company name in the "data" sheet.

can anyone pls help?


No luck finding an answer? You could always try Google.