VBA- Call Macro Per Sheet Issue

Question

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

The Subscript out of range error usually indicates that the sheet doesn't exist....are you sure you have a sheet called, "Boston"? — OpiesDad, Aug 30 '16 at 17:12
ReformatSummary, ReformatSheetAndAddDropdowns are both macros. The sheet names are correct. I was getting that error when creating the macro within ReformatSummary so I take the errors with a grain of salt. — plankton, Aug 30 '16 at 17:56
The Subscript out of range error indicates that the sheet does not exist. What if you do this? "Sub test() Dim ws As Worksheet Set ws = Sheets("Boston") End Sub" Do you still get the error? — OpiesDad, Aug 30 '16 at 19:49
In one of your other posts, I think you mentioned that you are opening other workbooks. Perhaps the sheet is in another workbook. The statement "Sheets(Boston)" is implying that you are looking for the sheets in the workbook that is running the code, not some other one that may be open. I think you would benefit from reading this, http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — OpiesDad, Aug 30 '16 at 19:52
What if you put "ws.Select" immediately before "End Sub" Does it still run? — OpiesDad, Aug 30 '16 at 19:54
What does it mean that it "wants you to select a macro to run?" — OpiesDad, Aug 30 '16 at 19:54
@OpiesDad it still works even with the `ws.select` added in there. Your other comment had me thinking that maybe it was thinking of referencing the other workbook that I open in my previous post, but it is closed by the time I get to the next line selecting sheet Boston. I explicitly listed the workbook name and then the sheet to make sure it wasn't referencing anything else and I got a new error: `Select method of Worksheet class failed` on `Workbooks("CurrentWorkbook.xlsm").Sheets("Boston").Select` — plankton, Aug 30 '16 at 20:08
What if you did this - "Sub test() Dim ws As Worksheet Set ws = Sheets("Summary") ws.Select Call ReformatSummary Set ws = Sheets("Boston") ws.Select End Sub" — OpiesDad, Aug 30 '16 at 20:44
@OpiesDad So this is where I initially ran into the first error `Subscript out of range`. It runs the macro I created successfully in the last post, but gets stuck when the other workbook opens, as if it skips the little break I put in to let the Bloomberg formulas calculate then move on and close the workbook, to move to the "Boston" sheet. — plankton, Aug 31 '16 at 14:56
So, if you run it with the Call to ReformatSummary, a subscript out of range error happens, but if you comment out that line and run it, the error doesn't happen? Sounds like we need to see the code to ReformatSummary. I would guess that either the error is happening in there or it is deleting or renaming the "Boston" sheet. — OpiesDad, Aug 31 '16 at 15:28
@OpiesDad The thing is, it works when I run it completely by itself. It's only when I have directions to another sheet/workbook after the macro has finished that it starts malfunctioning. It's like I need to end the macro after ReformatSummary and start it up again at the next step for it to work. I don't know how to do that programmatically. — plankton, Aug 31 '16 at 15:55
What I'm understanding is the following: If you run the code I created without calling the `ReformatSummary` procedure, then the line `Set ws = Sheets("Boston")` and `ws.Select` run perfectly. If you run the code I created, but including calling `ReformatSummary` then the line `Set ws = Sheets("Boston")` or `ws.Select` is causing the error, `Subscript out of range`. However, you do not think the problem is somewhere in the `ReformatSummary` procedure. Am I misunderstanding something? — OpiesDad, Aug 31 '16 at 17:37
@OpiesDad You are correct. However, when I run `ReformatSummary` by itself, it works correctly. When I try to have a `master macro` run `ReformatSummary` and then move to another sheet, "Boston" in this case, it causes an error in which it looks like `ReformatSummary` gets stopped 3/4's of the way through to move to the "Boston" sheet, which doesn't make sense. — plankton, Sep 01 '16 at 12:04
There is some sort of issue related to ReformatSummary. We would need to see the code for this procedure to help you. — OpiesDad, Sep 01 '16 at 19:16
What happens if you put the code at the end of the ReformatSummary procedure, but inside of it? Is there an error then? — OpiesDad, Sep 01 '16 at 19:16

OpiesDad · Answer 1 · 2016-08-30T19:50:51.687

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."

Sorry, I'm still not good with VBA. How would that look if I were just running the first macro ReformatSummary? I'm confused as to where that would go. — plankton, Aug 30 '16 at 19:36

VBA- Call Macro Per Sheet Issue

1 Answers1