Splitting a workbook

Hi,

I have a workbook with 27 sheets where 4 (A, B, C, D) of them are reference sheets and the rest are reporting sheets (1-23). I'd like to create 23 individual workbooks that consist of 4 reference sheets A, B, C, D and 1 reporting sheet. Sheet names for reporting sheets are stored in the named range "Subs"
Here is the code I am trying to use but it generates "Subscript out of Range" Error 9.

Please Login or Register  to view this content.

Please Help me understand why it would not work?

How are the reporting sheets named?

PS Can you add code tags when posting code?

Are there any other workbooks open?

Does this work?

Please Login or Register  to view this content.

Where did you get the error?

Did you try adding a worksheet reference for Range("Subs")?

The code works fine for me in a mocked up workbook if I change the For statement to this.

Please Login or Register  to view this content.

PS In the code that fails how is xPath declared?

The code should be run from a module, though running it from ThisWorkbook shoudn't make any difference.

As for the error you describe, the only thing I can think that might cause that would be that xPath was incorrectly declared, or there wasn't an active workbook open, both of which seem highly unlikely.

Any chance you could upload a sample workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

PS No need for data other than that in the 'Subs' list.

No problem.

PS A little tip to avoid that type of thing, add Option Explicit at the top of each module. That will force you to declare all variables which might be a little annoying at first but it'll also pick up typos.