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>
 

dUBBINS

Well-known Member
Joined
Feb 9, 2015
Messages
503
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.
 

dUBBINS

Well-known Member
Joined
Feb 9, 2015
Messages
503
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:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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:

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
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
 

Forum statistics

Threads
1,082,555
Messages
5,366,293
Members
400,882
Latest member
zaldy_a

Some videos you may like

This Week's Hot Topics

Top