CODEWITHSUNDEEP
vbaexcelmacros
plankton
plankton

Reputation: 325

VBA- Call Macro Per Sheet Issue

My newest question is in reference to this post. I have a Master macro that calls a macro per sheet in the workbook. I'm getting a Subscript out of range error in response to going from the macro in my previous post in the "Summary" sheet, to another macro in the next sheet. I think I can eliminate the error and make the Master macro work if I can eliminate the sheet .Select statement and call the macro while identifying the sheet in one line. Any help?

How it is now, with the error coming after Call ReformatSummary on Sheets("Boston").Select:

    Sub ReformatTheWorkbook()

    Sheets("Summary").Select
    Call ReformatSummary

    Sheets("Boston").Select
    Call ReformatSheetAndAddDropdowns

    Sheets("London").Select
    Call ReformatSheetAndAddDropdowns

    Sheets("Hong Kong").Select
    Call ReformatSheetAndAddDropdowns

End Sub

This is what I want to do but without the Sheet("name").Select having to identify the next sheet:

Sub ReformatTheWorkbook()

Sheets("Summary").Select
Call ReformatSummary

Application.Run "RefreshAllStaticData"
Application.OnTime Now + TimeValue("00:00:05"), "Part2RTW"

End Sub


Sub Part2RTW()

Sheets("Boston").Select
Call ReformatSheetAndAddDropdowns

Sheets("London").Select
Call ReformatSheetAndAddDropdowns

Sheets("Hong Kong").Select
Call ReformatSheetAndAddDropdowns

End Sub

Upvotes: 0

Views: 260

Answers (1)

OpiesDad
OpiesDad

Reputation: 3435

Here is an example of what Comintern made a comment about...you should pass the worksheet as a parameter:

Sub ReformatSummary(ws As Worksheet)
     'instead of ActiveSheet.Range("A1").Value = "Test" use:
      ws.Range("A1").Value = "Test"
End Sub
Sub ReformatSheetAndAddDropdowns(ws As Worksheet)
    ....Whatever you are doing to the sheets
End Sub

Sub ReformatTheWorkbook()
    Call ReformatSummary(Sheets("Summary"))
    Call ReformatSheetAndAddDropdowns(Sheets("Boston"))
    ....
End Sub

This will pass the worksheet as a parameter. Notice that when you use the parameter in the procedure, you need to use the worksheet instead of what you were likely doing as "ActiveSheet."

So, if you have code such as ActiveSheet.Range("A1").Value = "TestValue"

this will set cell A1 in the selected sheet equal to TestValue

Instead, you should use

   ws.Range("A1").Value = "TetsValue"

this will set the cell A1 in the sheet defined in object ws equal to TestValue

In this example, a worksheet object is being generated by the statement Sheets("Summary")

As noted in a comment, this doesn't match the error message you are having, though. The Subscript out of range error usually indicates that the object doesn't exist. In this case, I'd guess that there is no sheet named, "Boston."

Upvotes: 2

Related Questions