Forum Discussion
Solved
Excel VBA - Loop through sheets testing same cell for input box string/value
HansVogelaar - 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. ...
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
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
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!
Thanks as always!