Subscript Out of Range Runtime Error 9

JTL9161 · Feb 21, 2019

I have a macro that I run daily starting with a blank spreadsheet (Workbook "A" we'll call it). It imports a text file then formats and extracts data from the formatted text. When it finds a particular cell with data (Data "X") it copies it and opens another spreadsheet (Workbook "B") to paste that data to the current date line in that spreadsheet. It then goes back to the original spreadsheet (WB "A") copies another piece of data (data "Z") and back to WB "B" to paste it. It works fine when I open workbook "A" and go to the macro menu and choose to run the macro.

In an attempt to automate it. I took the entire macro out of the General tab in VB and moved it to the Workbook tab and added a message box prompt asking "do I want to run the macro" so when the workbook is opened I would just click yes and it runs the macro.

Except now it does the steps above, imports the text and finds the first piece of data (data "X"), opens WB "B" and pastes it goes back to WB "A" gets the 2nd piece of data ("Z") but then when it tries to go back to WB "B" a second time I get "Runtime Error '9' Subscript out of range". Which is weird because it successfully copied the first piece of data ("X") over but when it tries to find WB "B" the 2nd time it cannot and this only happened after I moved the macro into the Workbook section of VB.

This is the line that is highlighted when the error shows

Windows("2019 - RX FEED LOG.xlsx").Activate "this is Workbook "B" in the above description.

Why does the macro find WB "B" the first time but not the second and only when the macro is run from the Workbook section of VB. If I run the same macro manually from General tab in workbook "A" it works fine.

Little confused.
James

steve the fish · Feb 21, 2019

This is what i would do. Take the code from workbook_open macro and create a new macro with it. Call it macro1. Then call macro1 from the workbook_open macro.

Jeffrey Mahoney · Feb 21, 2019

A few things. You don't have to move the code from a standard module, you could just call it from "ThisWorkbook" module. We could improve your code if you post it. Activating workbooks back and forth isn't as efficient as storing the workbook/worksheet combo into a variable.

What is the name of the SUB in the "Workbook" tab?

Jeff

JTL9161 · Feb 21, 2019

Thanks. I ended up leaving the macro in a module and putting a message box and call action in the ThisWorkbook. Works fine.

Thanks again

Subscript Out of Range Runtime Error 9

JTL9161

Well-known Member

Excel Facts

steve the fish

Well-known Member

Jeffrey Mahoney

Well-known Member

JTL9161

Well-known Member

Similar threads

Forum statistics

Share this page

Subscript Out of Range Runtime Error 9

JTL9161

Well-known Member

Excel Facts

steve the fish

Well-known Member

Jeffrey Mahoney

Well-known Member

JTL9161

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock