Basic Loop Not Working - Help!

[crmau]

Hello,

The following code is not working to loop through each sheet. Basically I want to add one column to every sheet and then copy paste some values from each sheet.

The loop only works on the active sheet and ends up adding three columns, instead of one column to each of my three sheets. Where is the error?

Sub Formatting()


Dim sht As Worksheet
Dim lRow As Long
    For Each sht In Worksheets
     
            lRow = Cells(Rows.Count, 2).End(xlUp).Row
            Columns("A:A").Insert Shift = xlToRight, _
            CopyOrigin:=xlFormatFromLeftOrAbove
            Range("A5").Value = Range("B1").Value
            Range("A6:A" & lRow).Value = Range("B2").Value
        
    Next sht
End Sub

 

[Joe4]

This will loop through each sheet:

    For Each sht In Worksheets

but doesn't actually select or move to them. So your code continues to procress on the original sheet that was active when kicking off the code.

Try adding this:

    For Each sht In Worksheets
        [COLOR=#ff0000]sht.Activate[/COLOR]

 

[crmau]

This will loop through each sheet:

    For Each sht In Worksheets

but doesn't actually select or move to them. So your code continues to procress on the original sheet that was active when kicking off the code.

Try adding this:

    For Each sht In Worksheets
        [COLOR=#ff0000]sht.Activate[/COLOR]

Worked like a charm! Thanks!

 

[Joe4]

You are welcome.