Export specific sheets into 1, 2, or 3 workbooks, depending on cell value

Greetings-
I am attempting to export selected sheets of a master workbook to either 1, 2, or 3 new workbooks, depending on the value of cell C27 (which will be 1, 2, or 3). The filename of each new workbook will also vary depending on C27’s value. I’m a near-total novice at VBA, but this is what I’ve come up with. When I run the code, I get the “else without if” error. I’ve tried many different variations of If, Else, and
ElseIf… so the error is probably occurring because I am going about this task in a fundamentally wrong way. I cannot upload the workbook in question due to the security restrictions at my workplace. I am sure there are much better ways to do what I am attempting, but I am at the limit of my knowledge of VBA. Any assistance in fixing the code would be greatly appreciated.

Please Login or Register  to view this content.

Thanks- I've added those in. Still getting the "else without if" error, though.

XLadept- it runs, but does not actually save any files. I'll take another look at the code for mistakes I've made and let you know if I find anything. I really appreciate the help!

That started to work- it created a new workbook (titled Book1), with one worksheet (“Instructions”). However, the code stalls out there- error 400. The same outcome occurs when C27 contains 1, 2, and 3. I’ve double-checked the sheet names, and they are all correct. The sheets are all unprotected. Subsequent attempts produce the same error, but the numbers associated with the file names increase (Book2, Book3, etc.) Is there something screwy in here, maybe?

Please Login or Register  to view this content.

accidental double-post (deleted).

XLAdept- the new code runs... I think. I stopped it around the 4 minute mark, as I wasn't expecting much to happen after that point. It produced a workbook (Book1) with the correct tab names (but blank sheets) followed by a correct tab name with a 2, e.g., Information (2) that did contain the correct copied cells. The parts of the code dealing with naming the file, etc., might have run if I left it long enough. Thanks again for all of your help- I'll play around with it and see if I can resolve the multi-tab issue.

Progress! The pages copied with the correct sheet names and data, but workbook is still titled "Book2" (or Book3, or what-have-you). I'm going to play with it to try and figure out why the "save as" piece isn't grabbing the "ifs." Thanks again for the help!

No, it isn't. Running the code produces an unsaved workbook called Book(whatever). All the sheets are there, but it doesn't look like anything below "ws.Cells.Copy: ss.Range("A1").PasteSpecial xlPasteAll _ Next i" is working. No idea why, as of yet.

XLAdept- I finally got it working, using a different version of the "if" logic to make the "save as" play nicely with relativePath. I appreciate the help!

I just tried the new version of your code- it ran, but I got the same result... it produces a workbook with all of the correct sheets, but it's called Book1 and is unsaved. It must be something on my end if it's working for you.

Here is the code that is working for me, clumsy as it is. =) It now creates between 1 and 5 workbooks based on cell C24 (changed from C27), titled according to the relativePath. I just temporarily hide the sheets I don't want copied to the new workbooks.

Please Login or Register  to view this content.