Macro to sort

[bucci35]

Hi,
I have a workbook with 13 sheets, all of which have 9 "tables" that contain 3 columns each. The data will change over time. I want to be able to create 2 macro's on each sheet that sorts either column b or c. I have no problem creating for sheet 1, however is their a faster way to create the same for the other 12 sheets? Instead of recording the same thing 2 times for all sheets?
Hope this makes sense

Thanks
Dan

 

[Micron]

Loop over the sheets you want to apply the sort to. You might be able to simplify with a loop that uses a counter and the sheet code name. If you see Sheet6(My Data) in the vb editor, Sheet1 is the code name, My Data is what you see on the sheet tab. However, I've never figured out how to concatenate a counter variable to the code name and get it to work. I have seen a solution where all the sheets to be looped had 2 digits in the code name (so Sheet01, not Sheet1). Then it seems possible to look at the last 2 characters of the name in a loop.

 

[Micron]

See if this correctly picks the 13 sheets (assuming you can use the sheet codename). Change 2 in the counter accordingly. If it does, then you can incorporate the sheet1 code you already have.

Sub loopSheets()
Dim ws As Worksheet
Dim i As Integer
Dim strName As String

On Error GoTo errHandler
i = 1

For i = 2 To 14
     strName = "Sheet" & i
     Set ws = Worksheets(ActiveWorkbook.VBProject.VBComponents("Sheet" & i).Properties("Index"))
     Debug.Print ws.CodeName & "  " & ws.Name
Next
exitHere:
Set ws = Nothing
Exit Sub

errHandler:
If Err.Number = 9 Then
     Resume Next
Else
     MsgBox "Error " & Err.Number & ": " & Err.Description
     Resume exitHere
End If