VBA Refreshing macro w/ 2 workbooks open

I've searched for an answer for this for about the last week, I cant seem to find it. My problem is I have a workbook with a macro that is ran automatically every 15 seconds (or manually via a 'refresh' button). The workbook works fine when opened by itself, and when its opened on top of any other workbooks.

The problem arises when I have atleast 2 excel sheets open and have the workbook with the timing macro in the background and another workbook on the screen. I get the error "run-time error '9': subscript out of range"

when I hit debug this is the code that is highlighted

Please Login or Register  to view this content.

I have the "set wb = ('name of wb')" in my code. As suggested in a previous attempt to solve this problem.

Below is the code im running. Its purpose is to update the sheet named 'summary' with information from other sheets in the same workbook.

Please Login or Register  to view this content.

also of note, it doesnt matter if the other workbook has macros in it or not, i still recieve the error screen

[EDIT] Running Excel 2007, file is saved as a 'Excel Macro Enabled' workbook [/Edit]

Like I said I've been looking everywhere for a solution to this. If anyone can point me in the right direction I would appreciate it. ~TIA

Hi,

I did a test on your macro and realized that this problem occurs when the summary sheet is missing.
This means that the macro is running on the wrong workbook.
So I did qualify every reference to "sheets" with the workbook name like this: "wb.sheets" and the macro ran fine.

See the modified code, give it a try and let me know please.

Regards.

Please Login or Register  to view this content.