Macro to use data from closed excel

Hi All,
So I have a file 'A' which has two sheets 'a' and 'b'. I run a macro from Sheet 'a' which uses the data in sheet 'b'. Since both the sheets in the same file is increasing the file size by a lot ,I want to take out the sheet 'b' from File A and put it in the same folder as file 'A' in a separate file 'B'. I want to know how can I edit the macro to use the same sheet but from a closed file instead of in the same file.

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

How do I define the worksheet b when it is not in the same file and also will it work if the Macro is run when the File B is not open?

Thanks in Advance,
Aurka

There is a way for VBA to work on a file that is not open, but it is not straightforward. I strongly recommend that your code open the file then close when it's finished.

Please Login or Register  to view this content.