Split out worksheets then merge into master file

**pixifaery** · 05-27-2010, 05:05 AM

Hi there,

I have a spreadsheet which is too large for people to work on (takes too long to update) so thought the best option would be that once the file was ready to be used, all the files were split out and saved as individual workbooks (all the tab names would remain the same), thus preserving all the links. Once tasks have been completed another macro is executed to merge files into one workbook again. There would be a folder for each month and the previous months file would be copied and renamed to form the basis of the file for the next month.

The other option was to move files manually etc but when the folder containing all the files is copied into the next month the links wouldn't update due to the 255 characters being exceeded.

I did find a macro that did this but in order to get the path name correct think I need an input box so the user can select the month number (= folder)

Please Login or Register  to view this content.

Would be really grateful if someone could help me to amend the code...

Thanks

**tony h** · 05-27-2010, 05:19 AM

This allows you to enter a path for export files.
If you have formulae that go across sheets you wil have problems; particularly on trying to recombine them.

Please Login or Register  to view this content.

**pixifaery** · 05-27-2010, 05:52 AM

Hi Tony

Thanks for taking a look,

I'm getting an error message - to do with path not existing or already being used, this is the line of code that gets highlighted

Please Login or Register  to view this content.

Wonder if this has something to do with me amending the default in the input box.

Please Login or Register  to view this content.

I have been hunting for code (as am completely incompetent at devising my own) and found this which saves the file with the same path and filename as the active workbook. Think this would be better as will remove human error

Please Login or Register  to view this content.

Could you show me how to incorporate this? The other problem I have with both codes is that I need very specific worksheets to be moved i.e. "Error Report" and "Journal". The first section of code that you helped me will not move them.

Thanks for your help

**pixifaery** · 05-27-2010, 06:59 AM

Right, I've resorted back to the recorder as I find this simpler..and have got the following:

Please Login or Register  to view this content.

Each worksheet that is moved is saved under the same path as the master it originates from and the file name is the worksheet name. The only problem I have is that the folder 'M2' part of the pathway will change.

Would someone be able to help me?

Thanks