Getting a macro to work on multiple sheets

Microsoft Microsoft excel 2007 full vers...
August 29, 2010 at 06:31:21
Specs: Windows 7
I have 40 sheets with the names of all the golfers in our league in excel. The layout on all the sheets are the same and I would like to run a macro that sorts dates on all sheets simultaneously. Any suggestions would be appreciated. Thanks!

See More: Getting a macro to work on multiple sheets

Report •


#1
August 29, 2010 at 06:48:56
You can't sort the dates on all sheets "simultaneously" but you can sort them sheet by sheet.

Sub SortDatesBySheet()
  For ShtNum = 1 to Sheets.Count
   'Example code to sort data 
        Sheets(ShtNum).Range("A1:I5").Sort Key1:=Range("A1"), Order1:=xlAscending, _
         Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
         Orientation:=xlTopToBottom, _
         DataOption1:=xlSortTextAsNumbers
 Next
End Sub

Since it's real tough to see your spreadsheet from where we're sitting, that's about as specific as we can be.


Report •

#2
August 29, 2010 at 09:50:59
Hi,
Here is what my macro looks like now. This sheet is called "Bill". It has 2 columns of dates that get sorted (oldest to newest) I have 39 other sheets with player names where I would like to use the same macro to sort. What would I have to change in my macro to accomplish this. Thanks!

Sub AutoSort()
'
' AutoSort Macro
' Auto sort dates and scores
'
' Keyboard Shortcut: Ctrl+a
'
Range("C4:D23").Select
ActiveWorkbook.Worksheets("Bill").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Bill").Sort.SortFields.Add Key:=Range("C4:C23" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Bill").Sort
.SetRange Range("C4:D23")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("G4:H23").Select
ActiveWorkbook.Worksheets("Bill").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Bill").Sort.SortFields.Add Key:=Range("G4:G23" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Bill").Sort
.SetRange Range("G4:H23")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1:I2").Select
End Sub


Report •

#3
August 29, 2010 at 15:34:54
Well, I kind of answered your question in my first response.

If you are happy with the code you are using to sort a specific sheet ("Bill") then put your code inside a For-Next loop and replace "Bill" with the variable used in the loop.

One word of caution: In your code, you sometimes refer to the sheet by Name:

   ActiveWorkbook.Worksheets("Bill").Sort.SortFields.Clear

and sometime you don't:

   Range("C4:D23").Select

If you are going to try and access multiple sheets, you should always refer to each sheet specifically, otherwise the code will try to access the ActiveSheet.

 For ShtNum = 1 to Sheets.Count
   Sheets(ShtNum).Range("C4:D23").Select
   Sheets(ShtNum).Sort.SortFields.Clear
   etc.
Next



Report •

Related Solutions

#4
August 29, 2010 at 20:16:19
Thanks for the help. I was able to do what I want by changing "Worksheets("Bill")" to "ActiveSheet" in the macro wherever it appeared. Now, when I enter a different date on any player's sheet, I can run the macro to place the oldest date at the top of the sheet. Thanks again!

Report •

#5
August 29, 2010 at 20:27:15
If you want to sort the worksheet every time you add a date, use a Worksheet_Change macro to have it happen automatically as soon as you enter the date (or some other data in a specific column)


Private Sub Worksheet_Change(ByVal Target As Range)
'If change is made to Column A (1)
 If Target.Column = 1 Then
   ' Your sort code
 End If
End Sub


Report •


Ask Question