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
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,293
Office Version
2013
Platform
Windows
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.
 

GooberTron

Board Regular
Joined
Oct 13, 2012
Messages
205
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

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

GooberTron

Board Regular
Joined
Oct 13, 2012
Messages
205
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

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

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,293
Office Version
2013
Platform
Windows
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
 

Lambrix

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

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,293
Office Version
2013
Platform
Windows
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
 

Lambrix

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

Forum statistics

Threads
1,082,336
Messages
5,364,701
Members
400,811
Latest member
MSBINinja

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top