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

Question

HansVogelaar&nbsp;&nbsp;- guessing you're the guy to ask this VBA question.&nbsp;The attached sheet functions great, except one time - when a user opts to run the "Create New User Tab" prior to the new month.&nbsp; This causes an error when at the beginning of the next month,&nbsp; when the users must run the "Update Calcs" job.&nbsp; Update calcs inserts two new rows on all user sheets, and updates calculation references.&nbsp;&nbsp;&nbsp;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.&nbsp; 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-MMLooping through user sheets to check the value in A11 against inputbox new month valueskipping sheets where A11 = New MonthOn user sheets where A11 &lt;&gt; New Month value, then performing the current Update Calcs routine.&nbsp; The Update Calcs routine involves:inserting two empty rows above row 11 on all user sheetscopying rows 11 and 12 from the templatepasting all (formulas, formats, etc) from above step onto all user sheetsI'd love to try to accomplish this myself, but clearly some details are lost on me.&nbsp; &nbsp;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.&nbsp; The MS Help guides are, to be quite frank, lost on me as well.&nbsp;&nbsp;&nbsp;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?

hansvogelaar · Accepted Answer

JoeCavasin&nbsp;
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.
&nbsp;
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 &lt;&gt; "Template" And Ws.Name &lt;&gt; "User List" Then
            With Ws
                If Ws.Range("A11").Value &lt;&gt; NewMonth Then
                    .Rows(iRow).EntireRow.Insert
                    .Rows(iRow).EntireRow.Insert
                End If
            End With
        End If
    Next Ws

Forum Discussion

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

Share

Resources