Macro to seperate worksheets to seperate workbooks fired from external excel file

Hi,

I can find loads of macros that will seperate worksheets in a workbook to seperate workbooks - trouble is I cant find any that run from a seperate file.

EG: http://stackoverflow.com/questions/9...l-file-chooser
http://www.mrexcel.com/archive/VBA/5788.html

What I want to do is have an excel file called 'runme' so that when a new 'data' excel file (with the worksheets) is placed in a folder, a user can open the 'runme' file, press a button linked to a macro that then seperates the worksheets - this way the macro always exists. The data file keeps changing each month with last months data so I cant put it in there. I cant use the PERSONAL.XLS method as the users may vary and its only purpose is to seperate worksheets for this file.

Ideally if it could extract the new workbooks to a folder location that would be best so the user can always quickly delete the folder before running new data each month

Not very good with VBA - can someone help as to how to make a macro work on another workbook rather than the active one? For simplicity we can call the new data file 'newdata.xls' - working in excel 2003 - any help appreciated

Thanks,

Adam

There is no particular issue with a macro working on another excel file or files. You have to keep your mind clear and you typically use an object as a reference to the other file; the key change to the code you quote on MrExcel is the line

Set wbSource = ActiveWorkbook

If you replace this with

Please Login or Register  to view this content.

you are on your way. (It will prompt for the input file) You need a loop to process the file collection instead of prompting and chdir to set the folder before the save to meet that requirement. I hope this gives some clues; I don't have time tonight to complete the work. Hopefully I can complete later.

here is some code I prvided for another post: it allows a macro toi run through all the files in a folder tree. In your case the split sheets code would be modifeid and put inside the subroutine processfile

Please Login or Register  to view this content.

Thanks brynbaker