VBA code not looping through sheets

[matratus34]

Hi,
Would anyone be able to tell me why this code doesn’t loopto the next worksheet and only performs the task on the activesheet?

I realise my code is poor but this is just a tester for afew additional tasks to be performed within the loop – but at this stage I can’teven get the loop to work!
As always thanks for all the support!

Sub InsertColumn()
For Each ws In ActiveWorkbook.Worksheets
Columns("A:AH").Select
Selection.EntireColumn.Hidden = False
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A6").Select
ActiveCell.FormulaR1C1 = "Project"

Range("B3").Select
Selection.Copy
Range("A7").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A7:A399")
Next ws
End Sub

Thanks
Matt

 

[steve the fish]

When you say Columns, Range etc in VBA you are not qualifying those ranges with a sheet. All ranges have a corresponding sheet. Therefore in an attempt to be helpful VBA when faced with an unqualified range will use the activesheet at the time. You need to qualify your ranges with your created variable, ws, and get rid of your select lines or you need to select ws first. If you get stuck come back.

 

[RoryA]

You didn't qualify any of your ranges with a worksheet object. You need something like this:

Sub InsertColumn()
For Each ws In ActiveWorkbook.Worksheets
   with ws
      .Columns("A:AH").EntireColumn.Hidden = False
      .Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
      .Range("A6").Value = "Project"
      .Range("B3").Copy .Range("A7")
      .Range("A7").AutoFill Destination:=.Range("A7:A399")
   end with
Next ws
End Sub

 

[wideboydixon]

When you use Columns("A:AH") then it will apply to the active sheet. The quickest win is to add a line just under the For loop:

ws.Activate

Failing that, you'd have to change to:

ws.Columns("A:AH").Select

etc. although it would be even better to remove the reliance you have on the Selection object e.g.

ws.Columns("A:AH").EntireColumn.Hidden = False

Here's a re-written version with no Selection objects:

Sub InsertColumn()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    With ws
        .Columns("A:AH").EntireColumn.Hidden = False
        .Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        .Range("A6").Value = "Project"
        .Range("B3").Copy Destination:=.Range("A7")
        .Range("A7").AutoFill Destination:=.Range("A7:A399")
    End With
Next ws

End Sub

WBD

 

[matratus34]

Thanks for all the replies guys - I'll go and look at what you've told me and try to apply it into what I'm trying to do.

All your help is greatly appreciated as always.