Loop through a subset of worksheets will not work

[suremac]

Greetings,

I'm trying to insert two rows in a subset of worksheets in a workbook by defining a range in a worksheet and loop through the worksheets in that range, but the code will only run on the active worksheet.

Sub StatePIPData()
Dim sheet_name As Range
For Each sheet_name In Sheets("WS").Range("A:A")
If sheet_name.Value = "" Then
Exit For
Else
'Insert 2 rows for 2011 and 2012's data
Range("A14").EntireRow.Insert
Cells(14, 1) = Cells(15, 1) + 1
Range("A14").EntireRow.Insert
Cells(14, 1) = Cells(15, 1) + 1
End If
Next sheet_name
End Sub

Any thoughts on how I could get this loop to work?

Thank you very much,

 

[StephenCrump]

Here's some quick code that I think will do what you want. This assumes you want hard-coded values, not formulae, in A14:A15? If you want to include/exclude particular worksheets, you should test ws.Name

Sub StatePIPData()
    
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            .[$14:$15].Insert
            .[A15].Value = [A16].Value + 1
            .[A14].Value = [A15].Value + 1
        End With
    Next ws
    
End Sub