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?
VBA Code:
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