Looping macro issue

[davez]

Hi all, hoping someone can let me know what I am doing wrong here - I need to run a macro on all sheets within a workbook but the code below is only running it on the first sheet, any help?

Sub WorksheetLoop_RunMacroAllSheets()
 
Application.ScreenUpdating = False
Application.Calculation = xlManual

 
Dim i As Long
Dim shtCount As Long

shtCount = Sheets.Count

For i = 1 To shtCount

Call COPY_D1_O1_DOWN

Next i
         
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic

End Sub

 

[johnnyL]

You left out some code.

 

[StephenCrump]

Expanding on @johnnyL's comments ...

You haven't provided your code for COPY_D1_O1_DOWN. But how will that Sub know which sheet it's working on?

Sub Test()

    Dim i As Long
        
    For i = 1 To Sheets.Count
        Call COPY_D1_O1_DOWN
    Next i

End Sub
Sub COPY_D1_O1_DOWN()

    'Gets called multiple times.  But always operates on ActiveSheet
    Range("D1:O1").Copy Range("D2")

End Sub

Here's an alternative:

Sub Test2()
        
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        Call COPY_D1_O1_DOWN2(ws)
    Next ws

End Sub
Sub COPY_D1_O1_DOWN2(ws As Worksheet)

    With ws
        .Range("D1:O1").Copy .Range("D2")
    End With
    
End Sub

 

[davez]

Thanks guys, sorry if I was a bit vague. Anyway got this to work

Sub WorksheetLoop_RunMacroAllSheets()

    Dim xSh As Worksheet
   
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
   
    For Each xSh In Worksheets
        xSh.Select
        Call COPY_D1_O1_DOWN
       
    Next
   
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
   
End Sub