VBA for Saving Multiple Tabs to New File

[mmcconkie]

All,

Thank you for your help. I am trying to get a macro to generate reports for me, and save different tabs as different files. My macro will currently save EVERY tab as a new file, but I now want to spread out my data to be easier to read, so I'd like it to save every 2 tabs as a new file. I've attached a sample spreadsheet with my macro saved to it (note that the numbers were made up by me just now). I'd like to get the new macro to save the first two tabs as 1 file, and the second 2 tabs as another file. The code I'm currently using to save tabs as files is:

Please Login or Register  to view this content.

Any help you can give me would be greatly appreciated.

Thanks!

[humdingaling]

in your example sheet names are
44001_Summary
44002_detail
44003_Summary
44003_detail

are you looking to group every detail with summary ?
as logically 44001 and 44002 dont go together?
are you missing two tabs or does two actually go together? if the later...what logic makes them go together...just being next to each other?

[jindon]

Try change the loop to

Please Login or Register  to view this content.

[mmcconkie]

My mistake. The first tab should be labeled 44002_Summary followed by 44002_detail.

[humdingaling]

in that case jindon's solution should work perfectly for you as long as your same number Summary and detail sheets are right next to each other and your sequence is in the correct order

ie sheets
1,2 - 3,4 - 5,6 - etc

[mmcconkie]

I get an error saying: "Run-time error '424': Object required"

I now have the code reading:

Please Login or Register  to view this content.

Did I insert the command correctly?

[jindon]

Try change to

Please Login or Register  to view this content.

[mmcconkie]

Now the error says "Subscript out of range". The code currently reads:

Please Login or Register  to view this content.

[mmcconkie]

While I got that error. The files were still all created and saved! Thank you for your help!

[jindon]

Got no error.

Just added for the safety

Please Login or Register  to view this content.

for the safety...

[mmcconkie]

I'm still getting a subscript out of range error with the new FileFormat:=-4143 piece.

[jindon]

See the attached

Macro Demo revised.xlsm

[mmcconkie]

Worked like a charm! Thank you.