VBA Loop Copy and Paste, not looping through all worksheets

[csimonds]

Hello,

I have the below code, I was hoping it would look at all sheets in workbook, if N4=copy, then take B9-22 and G9-22 and paste in row 24. Then move to the next sheet with N4=copy and do the same. It works for one worksheet, but does not loop through the workbook. If anyone could please provide some advice as to why this code is not working it would be greatly appreciated.

Thank you

Sub CopyData()    Dim wks         As Worksheet


    With ActiveWorkbook
        For Each wks In ActiveWorkbook.Worksheets
            If LCase(wks.Range("N4").Value) = "copy" Then
                Range("B9:B22").Select
                Selection.Copy
                Range("B24").Select
                ActiveSheet.Paste
                
                Range("G9:G22").Select
                Selection.Copy
                Range("G24").Select
                ActiveSheet.Paste

                Range("A1").Select

            End If
        Next wks
    End With
End Sub

 

[tomvorves]

Activate the sheet on each loop
You can make this work by adding one line of code inside your For Each Loop
wks.Activate

Sub CopyData()
Dim wks As Worksheet
With ActiveWorkbook
For Each wks In ActiveWorkbook.Worksheets
wks.Activate
If LCase(wks.Range("N4").Value) = "copy" Then
Range("B9:B22").Select
Selection.Copy
Range("B24").Select
ActiveSheet.Paste

Range("G9:G22").Select
Selection.Copy
Range("G24").Select
ActiveSheet.Paste


Range("A1").Select


End If
Next wks
End With
End Sub

 

[LEE355]

No need for selecting cells and sheets.

Sub CopyData()
Dim wks As Worksheet


    For Each wks In ActiveWorkbook.Worksheets
        
        With wks
            
            If LCase(.Range("N4").Value) = "copy" Then
                .Range("B9:B22").Copy .Range("B24")
                .Range("G9:G22").Copy .Range("G24")
            End If
        
        End With
    
    Next wks
    
End Sub

 

[csimonds]

Thank you both for your replies. This is now working perfectly for my needs.

Thanks

No need for selecting cells and sheets.

Sub CopyData()
Dim wks As Worksheet


    For Each wks In ActiveWorkbook.Worksheets
        
        With wks
            
            If LCase(.Range("N4").Value) = "copy" Then
                .Range("B9:B22").Copy .Range("B24")
                .Range("G9:G22").Copy .Range("G24")
            End If
        
        End With
    
    Next wks
    
End Sub