Unable to loop through all worksheets with my code :(

[jwb1012]

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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




AFTER MACRO:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[dUBBINS]

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:

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.

 

[dUBBINS]

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.

 

[Jonmo1]

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

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

 

[Jonmo1]

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

 

[jwb1012]

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.

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