Unable to loop through all worksheets with my code :(

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
Hello, I am teaching myself VBA (with a lot of help from MrExcel) and I am running into an issue when I attempt to expand my macro to more than one worksheet.

I have successfully tested the code below, which searches column A - and when it finds a number, in column A, it inserts that many rows below that cell. But, when I attempt to get this code to loop through all worksheets in the workbook, it doesn't work and delivers an error. I believe it is the structure/arraignment/order of my argument.

Also, if possible - I would like to adjust the macro so it doesn't start until row 2 (my worksheets have a "page number" in cell A1, so it shouldn't add rows after this page number in cell A1).

P.S. - sorry if my "indenting" is off. I do not completely understand this logic yet.


SUCCESSFUL MACRO:
Sub Insert()
Dim End_Row As Long, n As Long, Ins As Long
Dim sh As Worksheets
End_Row = Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
For n = End_Row To 1 Step -1
Ins = Cells(n, "A").Value

If Ins > 0 Then Range("A" & n + 1 & ":A" & n + Ins).EntireRow.Insert
Next n
End Sub



UNSUCCESSFUL ATTEMPT FOR ALL WORKSHEETS:

Sub Insert()
Dim End_Row As Long, n As Long, Ins As Long
Dim sh As Worksheets
For Each sh In ActiveWorkbook.Sheets
If Left(sh.Name, 9) = "Labor BOE" Then

End_Row = Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False
For n = End_Row To 1 Step -1
Ins = Cells(n, "A").Value

If Ins > 0 Then Range("A" & n + 1 & ":A" & n + Ins).EntireRow.Insert
Next n

End If

Next sh
End Sub




BEFORE MACRO:
1NameHours
Grand Total
2NameHours
Grand Total
3NameHours
Grand Total

<tbody>
</tbody>




AFTER MACRO:

ABC
1NameHours
Grand Total
2NameHours
Grand Total
3NameHours
Grand Total

<tbody>
</tbody>
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
I am self taught, and I am sure someone else can give a better answer, but in case no one else responds, try:

End_Row = Range("A2" & Rows.Count).End(xlUp).Row

and to loop, I found success with:

Code:
Dim workSH As Integer
Dim worksheetEXISTS As Boolean
workSH = Application.Sheets.Count
worksheetsexits = False
    For n = 1 To workSH
        If Worksheets(n).Name = t Then
            worksheetEXISTS = True
                t = t + 1
                i = i + 1
    Exit For
        End If
Next n
    If worksheetEXISTS = False Then
    End If

I don't have time to alter it to fit your code, but maybe this will give you something to play with until an expert comes along. :)

Also, indenting doesn't matter as far as the code. There are preferences out there, but unless you are coding as a profession, I would recommend you indent however it makes your code easier to read for yourself.
 
Upvote 0
TIP: when you post put without spaces before your code:

[ c o d e ]

and after your code:

[ \ c o d e ]

That will encapsulate it in the box and makes it easier to read, and while most people are nice on here, some are grumpy and rude about not doing it.
 
Last edited:
Upvote 0
You have to qualify the sheet on your Range and Cells statements.

Rich (BB code):
Sub Insert()
Dim End_Row As Long, n As Long, Ins As Long
Dim sh As Worksheets
For Each sh In ActiveWorkbook.Sheets
    If Left(sh.Name, 9) = "Labor BOE" Then
        
        End_Row = sh.Range("A" & Rows.Count).End(xlUp).Row
        
        Application.ScreenUpdating = False
        For n = End_Row To 1 Step -1
            Ins = sh.Cells(n, "A").Value
            
            If Ins > 0 Then sh.Range("A" & n + 1 & ":A" & n + Ins).EntireRow.Insert
        Next n
        
    End If
Next sh
End Sub
 
Upvote 0
To make it skip Row 1.

Change this
For n = End_Row To 1 Step -1

to
For n = End_Row To 2 Step -1
 
Last edited:
Upvote 0
Thank you for the responses! When adjust the code as shown below, trying to loop through all worksheets that begin with "Labor BOE," it tells me "Run-time error '13': Type mismatch." Any thoughts?

Thanks again for the help!

You have to qualify the sheet on your Range and Cells statements.

Rich (BB code):
Sub Insert()
Dim End_Row As Long, n As Long, Ins As Long
Dim sh As Worksheets
For Each sh In ActiveWorkbook.Sheets
    If Left(sh.Name, 9) = "Labor BOE" Then
        
        End_Row = sh.Range("A" & Rows.Count).End(xlUp).Row
        
        Application.ScreenUpdating = False
        For n = End_Row To 1 Step -1
            Ins = sh.Cells(n, "A").Value
            
            If Ins > 0 Then sh.Range("A" & n + 1 & ":A" & n + Ins).EntireRow.Insert
        Next n
        
    End If
Next sh
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

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