How to split (copy) sheets from one workbook to individual workbooks?

[MariaR323]

So I have one workbook with about 50 worksheets (tabs). Is there a way to split each tab and save as their own workbook? Here's the quirk, the master workbook has one (lookup table) sheet that has to be copied with each of those other sheets.

Master Workbook (1 lookup tab + 49 tabs)
[lookup tab] [tab a] [tab b] [tab c] [tab d]....

So basically the [lookup tab] has to be copied with each of the rest of the tabs so I can save as a new workbook containing two tabs.

- new workbook 1 would contain [lookup tab] [tab a]
- new workbook 2 would contain [lookup tab] [tab b]
and so on...


I found the below VBA code in a search but I believe it's for single sheets only. Can anybody help?? I'd be very grateful as it would save me quite a bit of time.

Sub CreateNewWBS()
Dim wbThis As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim strFilename As String

Set wbThis = ThisWorkbook
For Each ws In wbThis.Worksheets
strFilename = wbThis.Path & "/" & ws.Name
ws.Copy
Set wbNew = ActiveWorkbook
wbNew.SaveAs strFilename
wbNew.Close
Next ws
End Sub

 

[MariaR323]

Glad to help & thanks for the feedback

Fluff, help please...

I've been working with my file for the last couple weeks and I just tried to run my macro again and it doesn't run at all.

Is it because I've copied the original file and renamed it? Like my first file was Draft A, now I'm on Draft D... do I need to delete and renter the macro again?

Thanks!

 

[Fluff]

As long as the new file is an .xlsm or .xlsb file then it should be ok.
Try going into the VB editor, click anywhere in the code & press F8 repeatedly, this will allow you to step through the code.
What happens?

 

[MariaR323]

I don't know what happened but what I did in the meantime was I deleted the macro. Resaved my file as a regular Excel workbook. Then opened it back up again, created the macro again, resaved as macro excel file and now it worked!

Whew!!! Thanks for responding!!!

 

[Fluff]

Glad to hear you got it sorted