VBA Looping through Worksheets and Calling Macros- Error 1004

Lambrix

Board Regular
Joined
Jun 29, 2012
Messages
62
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?

Code:
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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?

Code:
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.
 
Upvote 0
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?
 
Upvote 0
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:

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
 
Upvote 0
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).
 
Upvote 0
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:

Code:
'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
Code:
'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>
 
Upvote 0
Try is this way.
Code:
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
 
Upvote 0
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).

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
 
Upvote 0
Try it with these modificationbs.
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
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
 
Upvote 0
It failed on 'Step 1
Code:
 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.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top