Looping through a subset of worksheets with vba does not work

Question

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?

Answer 1

Is it what you need?

Sub StatePIPData()
    Dim sheet_name As Range
    Dim sh As Worksheet

    For Each sheet_name In ThisWorkbook.Worksheets("WS").Range("A:A")
        If sheet_name.Value = "" Then
            Exit For
        Else
            On Error Resume Next
            Set sh = ThisWorkbook.Worksheets(sheet_name)
            On Error GoTo 0

            If Not sh Is Nothing Then
                With sh
                    '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 With
            End If
        End If
    Next sheet_name
 End Sub