Run macro in multiple sheets at once

[JadonR]

I have the below macro to perform a series of tasks. However, I have to click on each worksheet and run the macro and would instead like to use "With Worksheets("SheetName")" instead. I defined using "With worksheets" but the macro only performs the tasks on the active worksheet and not all three. Can someone help me?

Sub AutoFill_and_Export_Test()
'
' AutoFill and Export Macro
'
Application.ScreenUpdating = False
'Clear worksheet rows 3 and below for sheet "DIM 1 Export"
[B]With Worksheets("DIM 1 Export")[/B]
    Rows("3:5000").Select
    Selection.Delete Shift:=xlUp
    End With
    With Range("A2:G2")
        .AutoFill Destination:=.Resize(Sheets("PO Worksheet").Cells(Rows.Count, "A").End(xlUp).Row - 1), Type:=xlFillDefault
    End With
   
'Clear worksheet rows 3 and below for sheet "DIM 2 Export"
With Worksheets("DIM 2 Export")
    Rows("3:5000").Select
    Selection.Delete Shift:=xlUp
    End With
    With Range("A2:G2")
        .AutoFill Destination:=.Resize(Sheets("PO Worksheet").Cells(Rows.Count, "A").End(xlUp).Row - 1), Type:=xlFillDefault
    End With

'Clear worksheet rows 3 and below for sheet "DIM 3 Export"
With Worksheets("DIM 3 Export")
    Rows("3:5000").Select
    Selection.Delete Shift:=xlUp
    End With
    With Range("A2:G2")
        .AutoFill Destination:=.Resize(Sheets("PO Worksheet").Cells(Rows.Count, "A").End(xlUp).Row - 1), Type:=xlFillDefault
    End With
Application.ScreenUpdating = True
End Sub

 

[frabulator]

You can use this and call whatever sheets you need.

Sub PreformOnWorksheets(ws As Worksheet)

    Application.ScreenUpdating = False
    With ws
        Rows("3:5000").Select
        Selection.Delete Shift:=xlUp

       With Range("A2:G2")
           .AutoFill Destination:=.Resize(Sheets("PO Worksheet").Cells(Rows.Count, "A").End(xlUp).Row - 1), Type:=xlFillDefault
       End With
    End With
    Application.ScreenUpdating = True

End Sub

call it with this code. You can set your sheet names in the collection.

Sub RunLoop()

    Dim col As Collection
    Set col = New Collection
   
    col.Add ("DIM 1 Export")
    col.Add ("DIM 2 Export")
    col.Add ("DIM 3 Export")
    'or really anything you want
   
    For i = 1 To col.Count
        Dim ws As Worksheet
        Set ws = ActiveWorkbook.Worksheets(col.Item(i))
        PreformOnWorksheets ws
       
    Next
End Sub

 

[mumps]

Try:

Sub AutoFill_and_Export_Test()
    Application.ScreenUpdating = False
    Dim x As Long
    For x = 1 To 3
        With Worksheets("DIM " & x & " Export")
            .Rows("3:5000").Delete Shift:=xlUp
            With .Range("A2:G2")
                .AutoFill Destination:=.Resize(Sheets("PO Worksheet").Cells(Sheets("PO Worksheet").Rows.Count, "A").End(xlUp).Row - 1), Type:=xlFillDefault
            End With
        End With
    Application.ScreenUpdating = True
End Sub

 

[JadonR]

Try:

Sub AutoFill_and_Export_Test()
    Application.ScreenUpdating = False
    Dim x As Long
    For x = 1 To 3
        With Worksheets("DIM " & x & " Export")
            .Rows("3:5000").Delete Shift:=xlUp
            With .Range("A2:G2")
                .AutoFill Destination:=.Resize(Sheets("PO Worksheet").Cells(Sheets("PO Worksheet").Rows.Count, "A").End(xlUp).Row - 1), Type:=xlFillDefault
            End With
        End With
    Application.ScreenUpdating = True
End Sub

This gives me an error.

 

[JadonR]

You can use this and call whatever sheets you need.

Sub PreformOnWorksheets(ws As Worksheet)

    Application.ScreenUpdating = False
    With ws
        Rows("3:5000").Select
        Selection.Delete Shift:=xlUp

       With Range("A2:G2")
           .AutoFill Destination:=.Resize(Sheets("PO Worksheet").Cells(Rows.Count, "A").End(xlUp).Row - 1), Type:=xlFillDefault
       End With
    End With
    Application.ScreenUpdating = True

End Sub

call it with this code. You can set your sheet names in the collection.

Sub RunLoop()

    Dim col As Collection
    Set col = New Collection
  
    col.Add ("DIM 1 Export")
    col.Add ("DIM 2 Export")
    col.Add ("DIM 3 Export")
    'or really anything you want
  
    For i = 1 To col.Count
        Dim ws As Worksheet
        Set ws = ActiveWorkbook.Worksheets(col.Item(i))
        PreformOnWorksheets ws
      
    Next
End Sub

So I saved both of these into Module 1 and when I run "Sub RunLoop", it still only performs on the active sheet. What am I doing wrong?

 

[mumps]

What is the error message?

 

[JadonR]

Disregard, it works.

 

[mumps]

Glad it worked out.