simplifying macro to repeat steps in every sheet

**roth_nj** · 05-28-2009, 08:47 AM

i have a book set up with a few sheets with info seperated out and then a sheet contianing everything. i have a macro set up to copy and paste from one sheet into a collection sheet, and then go thru and sort all the sheets.

in my macro, i have a section for each indivitual sheet. i want to make it so it goes thru and just performes the task for all of them. i need this done so if we add more sheets in later, the macro doesnt have to be edited...

below is my macro

Sub Macro1()
' Keyboard Shortcut: Ctrl+z

    ActiveSheet.Cells(Rows.Count, "A").End(xlUp).EntireRow.Copy
    Worksheets("ALL").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial
    Application.CutCopyMode = False
    
    Sheets("ALL").Select
    Cells.Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
    Sheets("BMI").Select
    Cells.Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Sheets("ODC").Select
    Cells.Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Sheets("SNR").Select
    Cells.Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Sheets("XAC").Select
    Cells.Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Sheets("DPY").Select
    Cells.Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
End Sub

**royUK** · 05-28-2009, 09:44 AM

You don't need to select the sheet or range

Dim ws As Worksheet

   For Each ws In ThisWorkbook.Worksheets
   ws.UsedRange.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
  Next ws

**roth_nj** · 05-28-2009, 09:50 AM

compile error: invalid or unquilifed reference

.UsedRange is highlighted

**royUK** · 05-28-2009, 10:21 AM

I've amended the previous code

**roth_nj** · 05-28-2009, 10:28 AM

run-time error 1104
debug

   ws.UsedRange.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

is highlighted yellow...

help says:

Ambiguous selection

Either you have not selected a keyword or you have requested Help on a component of the integrated development environment (IDE). If you were trying to select a keyword, try reselecting a single keyword. If you were trying to get information on the IDE, click one of the following:

**Leith Ross** · 05-28-2009, 10:19 PM

Hello roth_nj,

The named argument Key1 needs to be qualified with the worksheet variable ws, otherwise Excel assumes the range is on the active sheet. Also the active sheet should be excluded from the sorting loop.

Sub Macro1()

  Dim ws As Worksheet

    ActiveSheet.Cells(Rows.Count, "A").End(xlUp).EntireRow.Copy
    Worksheets("ALL").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial
    Application.CutCopyMode = False
    
    For Each ws In ThisWorkbook.Worksheets
      If ws.Name <> ActiveSheet.Name Then
        ws.UsedRange.Sort Key1:=ws.Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
                         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
      End If
    Next ws
   
End Sub