Make the current worksheet the active sheet.

[JohnZ1156]

I have a macro that I would like to be able to run on different worksheets. I placed a "button" on Sheet1 and assigned this macro:
Problem is, I'd like to place the same button on Sheet2, Sheet3, etc. and run the same macro, except on the current sheet, not always on Sheet1.
I know the "fix" is somewhere in the line Set ws = Worksheets("Sheet1") but I don't know how to "fix" it.

Option Explicit
Sub L_to_R()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, c As Range
    Set ws = Worksheets("Sheet1")
    For Each c In ws.Range("A2", Cells(Rows.Count, "A").End(xlUp))
        If IsDate(c) Then c.Offset(, 1).Resize(, 5).Sort _
        Key1:=c, Order1:=xlAscending, Orientation:=xlLeftToRight
        ws.Sort.SortFields.Clear
    Next c
    Application.ScreenUpdating = True
End Sub

 

[RoryA]

You need:

Set ws = activesheet

 

[JohnZ1156]

Thanks Rory
After I made that change, now I get this:

 

[Skyybot]

I think row number has to be declared with Offset. i.e. - Also you forgot the End If statement

c.Offset(0,1).Resize(0,5)

 

[Skyybot]

Thanks Rory
After I made that change, now I get this:
View attachment 102414

Please post VBA code as text with VBA code tags instead of a picture.

 

[Skyybot]

Sub L_to_R()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, c As Range
    Set ws = ActiveSheet
    For Each c In ws.Range("A2", Cells(Rows.Count, "A").End(xlUp))
        If IsDate(c) Then
            c.Offset(0, 1).Resize(0, 5).Sort _
            Key1:=c, Order1:=xlAscending, Orientation:=xlLeftToRight
            ws.Sort.SortFields.Clear
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

 

[RoryA]

You are trying to sort a range by a cell that is not part of the range.

 

[JohnZ1156]

I created a "dummy" worksheet to try to figure out why the macro wasn't working.

I originally had my data formatted as Excel Tables. When I tried to run the above macro, I got that VBA error message. Once I converted the tables back to normal ranges, the macro worked. Go figure! I don't understand why!

After experimenting around, I finally did get the same macro to work on multiple sheets. I placed a "button" on each sheet and assigned the same macro to each button. When I selected the sheet and pressed the button, it ran the macro on that sheet.

If someone can explain to me why this macro would not work on data formatted as an Excel Table, but did work when the same data was not formatted as an Excel Table, I'd love to understand.

In fairness, I don't believe I ever said that my data was formatted as an Excel Table.
Regardless, Thank you all for your help and suggestions.

 

[MARK858]

I think row number has to be declared with Offset. i.e.

Just to say it doesn't (although some frown upon it being omitted)

Sub Skybot_test()
Debug.Print "With row omitted the address is " & Range("D4").Offset(, 4).Address
Debug.Print "With row included the address is " & Range("D4").Offset(0, 4).Address
End Sub

c.Offset(0, 1).Resize(0, 5).Sort

Resize on the other hand won't accept a 0

Sub Skybot_test()
Debug.Print "With row omitted the address is " & Range("D4").Resize(, 5).Address
Debug.Print "With row included the address is " & Range("D4").Resize(0, 5).Address
End Sub

 

[Skyybot]

Resize on the other hand won't accept a 0

Thanks @MARK858 . I did not know that. I have never used .Resize before. That's one of the reasons I love this Forum.