VBA Looping through Worksheets and Calling Macros- Error 1004

[Lambrix]

Greetings,

I am trying to Loop through each of the worksheets in my workbook and call 5 separate macros on each sheet. However I am getting an error when I run get the "Run-time error '1004': Method 'Select' of Object'_Worksheet'failed" error message.

Any ideas why I am getting this error?

Sub Looping ()

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Select

Call Step1
Call Step2
Call Step3
Call Step4
Call Step5

Next

End Sub

 

[timfritsch]

I was able to duplicate your error - for me, it was because the sheet was not activated. Try:

ws.cells(1, ws.columns.count).end(xltoleft).select
[\code]

This specifies the worksheet for the columns count.


Tim

 

[Lambrix]

I'm trying to simplify as much as possible to isolate the problem.

Sub loops()

Dim ws As Worksheet
For Each ws In Worksheets

With ws


Cells(1, Columns.Count).End(xlToLeft).Offset(25, 1).Range("A1"). _
Offset(-24, -3).Range("A1:D1").EntireColumn.Insert , _
CopyOrigin:=xlFormatFromLeftOrAbove

End With
Next


End Sub

Even with this code, it doesn't run in all the sheets, instead it runs multiple times in the activesheet. Any thoughts why this is happening?

 

[timfritsch]

After the With ws statement, you shouldto precede the cell references with a ".", otherwide Excel is acting on only the active sheet.

Cells(1, Columns.Count) becomes .Cells(1, .Columns.Count)

Try adding this identifier and see if you get better results.


Tim

 

[Lambrix]

After the With ws statement, you shouldto precede the cell references with a ".", otherwide Excel is acting on only the active sheet.

Cells(1, Columns.Count) becomes .Cells(1, .Columns.Count)

Try adding this identifier and see if you get better results.


Tim

VBA is bent on giving me a hard time today. Added the code mods as you suggested:

Sub loops()

Dim ws As Worksheet
For Each ws In Worksheets

With ws

.Cells(1, Columns.Count).End(xlToLeft).Offset(25, 1).Range("A1"). _
Offset(-24, -3).Range("A1:D1").EntireColumn.Insert , _
CopyOrigin:=xlFormatFromLeftOrAbove

End With
Next


End Sub

This time is says, "Run-time error '1004: APplication-defined or object-defined error

 

[timfritsch]

What are you trying to accomplish with this code? As I read it:

With the last column in row 1 in worksheet ws, offset by 25 rows and offset by 24 rows and 3 columns and insert columns - and do this all at the same time in one step.

It does not make sense to me. Can you describe in plain english what you are trying to do?


Tim

 

[Lambrix]

What are you trying to accomplish with this code? As I read it:

With the last column in row 1 in worksheet ws, offset by 25 rows and offset by 24 rows and 3 columns and insert columns - and do this all at the same time in one step.

It does not make sense to me. Can you describe in plain english what you are trying to do?


Tim

Yes I should probably explain the madness. I am Inserting 4 columns into the spreadsheet (As Additional Months), to the left of 3 fixed columns (Summary and Explanation/Notes)

Columns Format: Network Names, Months... Jan-Nov(4 columns for each month, Summary Columns (2 Columns), Explanation/Notes Column (1 Column).

The Macro Highlights all the Information in the 3 Columns including Summary and Explanation/Notes, grabs an additional column and then inserts. The inserts. The reason for the additional Offsetting is that Row 1 is merged, so I need to offset a few times to select the correct range.

It works great when running it on individual sheets, but when I try to include a loop it errors.

 

[timfritsch]

I'm sorry, but I can't picture what your workbook looks like. I'm out of ideas.


Tim