Excel VBA - Loop through sheets testing same cell for input box string/value

[JoeCavasin]

@Hans Vogelaar  - guessing you're the guy to ask this VBA question.

The attached sheet functions great, except one time - when a user opts to run the "Create New User Tab" prior to the new month.  This causes an error when at the beginning of the next month,  when the users must run the "Update Calcs" job.  Update calcs inserts two new rows on all user sheets, and updates calculation references.  

To work around this error, i've attempted to do the following, but i am getting hung up on the looping and testing cell A11 value in all user sheets.  So I've removed the few test mods i had, but the goal is:

• using an input box to receive the value "New Month" in format YYYY-MM
• Looping through user sheets to check the value in A11 against inputbox new month value
• skipping sheets where A11 = New Month
• On user sheets where A11 <> New Month value, then performing the current Update Calcs routine.  The Update Calcs routine involves:
  • inserting two empty rows above row 11 on all user sheets
  • copying rows 11 and 12 from the template
  • pasting all (formulas, formats, etc) from above step onto all user sheets

I'd love to try to accomplish this myself, but clearly some details are lost on me.   I understand the input box data is received as a string, and that has implications for comparing against cell A11 values, but the looping structure is also a continuing pain point for me.  The MS Help guides are, to be quite frank, lost on me as well.  

Do you have any good references i could read through to try and better understand how to accomplish this, or was it more trial and error for you?

[#2 · Accepted solution]

@JoeCavasin 

NewMonth is a string variable, so you refer to its value by using its name: NewMonth.

A string is not an object, so you should not use NewMonth.Value.

As far as I can tell (I have not tested the code, for I don't want to close all other workbooks), the first loop in TESTUpdateCalcsV2 should be

    For Each Ws In ThisWorkbook.Worksheets
        If Ws.Name <> "Template" And Ws.Name <> "User List" Then
            With Ws
                If Ws.Range("A11").Value <> NewMonth Then
                    .Rows(iRow).EntireRow.Insert
                    .Rows(iRow).EntireRow.Insert
                End If
            End With
        End If
    Next Ws

[#3]

and as always, it's the absolute simplest oversight on my end that makes the whole thing work. I had tried though can't recall now, how i had originally tried referring to it by name, but was getting a compile error... and yet, in this version it worked perfectly.

Thanks as always!