Sheet loop not working

[Lizard07]

Hi - I have a code that I would like to loop through each sheet (except the sheets named "Activity Summary" and "Control Panel"), copy column H, then copy the contents of cell B7 into column G.

' Loop through all worksheets and copy driver name into column G
'For Each sh In ActiveWorkbook.Worksheets
' If sh.Name <> "Control Panel" And sh.Name <> "Activity Summary" Then
' Columns("H:H").Select
' Selection.Copy
'Columns("G:G").Select
'Range("G7").Activate
'ActiveSheet.Paste
'Range("B7").Select
' Selection.Copy
'Range("G15").Select
'Range(Selection, Selection.End(xlDown)).Select
'ActiveSheet.Paste
'Next

Any ideas?

 

[kpark91]

They're commented. remove apostrophes at front?

 

[wigi]

Something like this will be much closer:

Sub loops()
'Loop through all worksheets and copy driver name into column G
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> "Control Panel" And sh.Name <> "Activity Summary" Then
            sh.Columns("H").Copy sh.Range("G7")
            sh.Range("B7").Copy sh.Range("G15", sh.Range("G15").End(xlDown))
        End If
    Next
End Sub

Inform yourself about the use of the variable called sh which qualifies the worksheet you are working on - within the loop.

PS: please use

 tags when you paste code on the forum.

 

[Lizard07]

Yah I'm aware of that, but when I run the Macro (without the comment apostrophes) I receive the error message "Next without for"

 

[wigi]

Yah I'm aware of that, but when I run the Macro (without the comment apostrophes) I receive the error message "Next without for"

You are missing an "End If" line.

Did you experiment with my suggestion?

 

[chicosbailbonds]

A commented line means that VBA thinks its a note for the code and doesn't perform anything.

 

[Lizard07]

Yes I did and I received this error message "Application-defined or object-defined error"

Sub loops()
'Loop through all worksheets and copy driver name into column G
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "Control Panel" And sh.Name <> "Activity Summary" Then
sh.Columns("H").Copy sh.Range("G")
sh.Range("B7").Copy sh.Range("G15", sh.Range("G15").End(xlDown))
End If
Next
End Sub

The only thing I've changed is the bolded code above, so the copy and paste ranges would match in size.

 

[kpark91]

Use code tags when posting codes as per forum rules

Try

Sub loops()
'Loop through all worksheets and copy driver name into column G
    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> "Control Panel" And sh.Name <> "Activity Summary" Then
            sh.Columns("H").Copy sh.Columns("G")
            sh.Range("B7").Copy sh.Range("G15", sh.Range("G15").End(xlDown))
        End If
    Next
End sub

 

[Lizard07]

Thank-you! That worked perfectly

 

[kpark91]

thanks for the feedback.