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

 

[JLGWhiz]

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

ws.Select

You don't need this line.

 

[GooberTron]

Do any of your other macros do any sheet selecting? I can't see anything wrong with the syntax. Does it fail before it even gets to the first call if you debug and step through the code?

 

[Lambrix]

Do any of your other macros do any sheet selecting? I can't see anything wrong with the syntax. Does it fail before it even gets to the first call if you debug and step through the code?

No, I don't have any sheet selecting going on. When I run the macro now, it loops the same thing over again on the same sheet. For some reason It's not running the macro on the other sheets in the workbook. I consolidated the macros in hopes of it solving the issue, but the problem persists. Here is the full code:

Sub Looping()
Dim MonthData As String
MonthData = InputBox("What Month Are you Reporting on?", "Input Box Text")
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets


' Insert the Finished Code here!
'Step 1
Cells(1, Columns.Count).End(xlToLeft).Select
ActiveCell.Offset(25, 1).Range("A1").Select
ActiveCell.Offset(-24, -3).Range("A1:D25").Select
'Step 2
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
'Step 3
  
Cells(1, Columns.Count).End(xlToLeft).Select
ActiveCell.Offset(1, 1).Range("A1").Select
ActiveCell.Offset(0, -7).Range("A1:D1").Select

With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    
If MonthData <> "" Then
        '
        ' If so, display it
        '
        'MsgBox inputData
               
    ActiveCell.Value = MonthData
        
    End If
    
'Step 4
Cells(1, Columns.Count).End(xlToLeft).Select
ActiveCell.Offset(1, 1).Range("A1").Select
ActiveCell.Offset(1, -11).Range("A1:D24").Select
Selection.Copy
ActiveCell.Offset(0, 4).Range("A1").Select
    ActiveSheet.Paste
'Step 5
Cells(1, Columns.Count).End(xlToLeft).Select
ActiveCell.Offset(3, 1).Range("A1").Select
ActiveCell.Offset(0, -7).Range("A1:B22").Select
Selection.ClearContents

Next
End Sub

 

[GooberTron]

I see why you might have selected worksheets now. As JLGWhiz suggested the general VBA rule of thumb should be that you don't need to select things.
However........
In the code above you may want to try replacing anything that says Cells to ws.Cells so the code then knows what worksheet you are referring to (i.e. the one in the loop).

 

[Lambrix]

However........
In the code above you may want to try replacing anything that says Cells to ws.Cells so the code then knows what worksheet you are referring to (i.e. the one in the loop).

So would this be correct then:

'Step 1 ws.Cells(1, Columns.Count).End(xlToLeft).Select ActiveCell.Offset(25, 1).Range("A1").Select ActiveCell.Offset(-24, -3).Range("A1:D25").Select 'Step 2 Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove 'Step 3    ws.Cells(1, Columns.Count).End(xlToLeft).Select

Becomes

'Step 1 ws.Cells(1, Columns.Count).End(xlToLeft).Select ActiveCell.Offset(25, 1).Range("A1").Select ActiveCell.Offset(-24, -3).Range("A1:D25").Select 'Step 2 Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove 'Step 3    ws.Cells(1, Columns.Count).End(xlToLeft).Select

?
</pre>

 

[JLGWhiz]

Try is this way.

Sub Looping ()
For i = 1 To ActiveWorkbook.Sheets.Count
 Sheets(i).Activate
 Call Step1
 Call Step2
 Call Step3
 Call Step4
 Call Step5 
Next
End Sub

 

[Lambrix]

I really can't figure out why it wont properly loop through the sheets. If I run the macro manually on the sheets individually, it works fine. When I try to include a loop, it reruns the script on one sheet over and over again (14 times).

Sub Looping()
Dim MonthData As String
MonthData = InputBox("What Month Are you Reporting on?", "Input Box Text")
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets


' Insert the Finished Code here!
'Step 1
Cells(1, Columns.Count).End(xlToLeft).Select
ActiveCell.Offset(25, 1).Range("A1").Select
ActiveCell.Offset(-24, -3).Range("A1:D25").Select
'Step 2
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
'Step 3
  
Cells(1, Columns.Count).End(xlToLeft).Select
ActiveCell.Offset(1, 1).Range("A1").Select
ActiveCell.Offset(0, -7).Range("A1:D1").Select

With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    
If MonthData <> "" Then
        '
        ' If so, display it
        '
        'MsgBox inputData
               
    ActiveCell.Value = MonthData
        
    End If
    
'Step 4
Cells(1, Columns.Count).End(xlToLeft).Select
ActiveCell.Offset(1, 1).Range("A1").Select
ActiveCell.Offset(1, -11).Range("A1:D24").Select
Selection.Copy
ActiveCell.Offset(0, 4).Range("A1").Select
    ActiveSheet.Paste
'Step 5
Cells(1, Columns.Count).End(xlToLeft).Select
ActiveCell.Offset(3, 1).Range("A1").Select
ActiveCell.Offset(0, -7).Range("A1:B22").Select
Selection.ClearContents

Next
End Sub

 

[JLGWhiz]

Try it with these modificationbs.

Sub Looping()
Dim MonthData As String
MonthData = InputBox("What Month Are you Reporting on?", "Input Box Text")
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
' Insert the Finished Code here!
'Step 1
ws.Cells(1, Columns.Count).End(xlToLeft).Select
ActiveCell.Offset(25, 1).Range("A1").Offset(-24, -3).Range("A1:D25").Select
'Step 2
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
'Step 3  
ws.Cells(1, Columns.Count).End(xlToLeft).Select
ActiveCell.Offset(1, 1).Range("A1").Select
ActiveCell.Offset(0, -7).Range("A1:D1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge    
    If MonthData <> "" Then        '
        ' If so, display it        '
        'MsgBox inputData               
    ActiveCell.Value = MonthData        
    End If    
'Step 4
ws.Cells(1, Columns.Count).End(xlToLeft).Select
ActiveCell.Offset(1, 1).Range("A1").Select
ActiveCell.Offset(1, -11).Range("A1:D24").Copy ActiveCell.Offset(0, 4).Range("A1")
'Step 5
Cells(1, Columns.Count).End(xlToLeft).Select
ActiveCell.Offset(3, 1).Range("A1").Select
ActiveCell.Offset(0, -7).Range("A1:B22").Select
Selection.ClearContents
Next
End Sub

 

[Lambrix]

It failed on 'Step 1

 ws.Cells(1, Columns.Count).End(xlToLeft).Select

Giving the error, "Run-time error '1004': Select Method of Range Class failed"

As you suggested earlier, could the selection in the code being causing the issue? I want to get away from using .select in my code, but I am dealing with merged header columns. I use offset and select to navigate around the merged columns because it's the only way I know how.