Split Sheets Into Workbooks Excel VBA Macro

In this video we see how split or copy sheets to separate workbooks in Excel using VBA macros. Each new workbook takes the name of the sheet and contains one single sheet with its original contents. Find the code to split or separate sheets into new workbooks with Excel VBA in the Excel Macro Class blog under the following link: excelmacroclass.blogspot.com/...
You can find many other macro examples and Excel VBA learning materials (including a training for beginners) in the blog under the link: excelmacroclass.blogspot.com/
And yet, if you want more, you can find various Excel applications of different nature in the other blogs of the Excel Macro Mania saga:
Excel Macro Fun (excelmacrofun.blogspot.com/)
Excel Macro Business (excelmacrobusiness.blogspot.com/)
Excel Macro Sports (excelmacrosports.blogspot.com/)

[@PiyushMedicalKnowledge]

First like from my side

[@razamoulvi8801]

Great Code sir.👌🏻👌🏻
So comfortable and easy to use..
Thanks for the code

[@MohAboAbdo]

Thank you so much

[@omarwameedh191]

second like from my side 😊 keep uploading such videos

[@PiyushMedicalKnowledge]

Keep uploading such videos

[@VictBond7]

Great ❤ 🔥

[@m.n.953]

nice

[@juliamacha1028]

Hey, do you know how to pull out only columns with specific names (for example only model columns) form each worksheet into separate workbooks?

[@ExcelMacroMania]

The code to do that would probably look to what was explained in this other video (Copy Columns To Separate Sheets): ruclips.net/video/SLjSWCNW0qk/видео.htmlsi=80VETjpRMJ63wzjj
In addition to what's covered in that video, you would probably have to add a condition inside the loop to check if the column "header" matches the name you want to copy. Then you would combine that with this one to create a workbook for each sheet. Of course, you can do it all at once as well.

[@PappaG]

Excellent code, simple and effective.
Q - is it possible to exclude certain sheets from copying rather than copying all sheets?

[@ExcelMacroMania]

Sure it is. You just need to add that condition inside the For Each loop. For example, let's say you just want to copy sheets with a given name (data1, data2, and data3, only), you would add the following:
...
For Each ws in ....
If ws.Name = "data1" Or ws.Name = "data2" Or ws.Name = "data3" Then
... code to copy ws to other workbook here
End If
Next ws

[@PappaG]

Thank you for the prompt reply.
So if you did not want data 1, data 2 etc to be copied but the remaining sheets to be copied is the change:
If NOT ws.name = "data1" or ws.name = "data2" then
I'm going to use ws.move but don't want certain sheets to be moved in short

[@ExcelMacroMania]

@@PappaG Yes you can use that or simply the symbol. But only if you have many sheets and want to exclude a few, otherwise, you could just keep the other structure to move those that match the criteria (nothing happens to the others). Using instead of NOT is as follows:
If ws.Name "data4" Then
...this does something with all the other sheets, for example data1, data2, and data3, but NOT with data4.
End If

[@rldb]

Can u please explain how to export only the selected sheets instead of all the sheets? TIA

[@ExcelMacroMania]

Change this in the code: For Each ws In ActiveWindow.SelectedSheets

[@rldb]

@@ExcelMacroMania Thank you very much.
Do you have a video on the code to rename multiple sheets to a cell value in each sheet. It would be very useful for exporting multiple sheets generated from the Pivot Table Drilldown.

[@ExcelMacroMania]

@@rldb Considering you have the list of names in column A (starting in A1) you could do something like this to rename each worksheet with the name in that list:
Sub RenameSheetNames()
Dim ws As Worksheet, r As Integer
For Each ws In Worksheets
r = r + 1
ws.Name = Range("A" & r).Value
Next ws
End Sub

[@rldb]

@@ExcelMacroMania That is really cool. But I think I should have been a bit more clear about the requirement. I have generated region wise sheet from a pivot table. Each sheet has the region name in a particular cell (say, B5). I should be able to input the cell reference in a prompt in the macro or the macro should be able to read a certain cell of each sheet and rename it accordingly. Hope I am clear now.

[@ExcelMacroMania]

@@rldb First you will need to add an event procedure to ThisWorkbook (Workbook_NewSheet event) that triggers when you generate a new sheet with the table extracted from your pivot (manually). Then you could get the name of that table, navigate to the second row in the first column (assuming that's the name you want to get), and change the worksheet name. That would look like this:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
newTableFromPivot = ActiveCell.ListObject.Name
newSheetName = ActiveSheet.ListObjects(newTableFromPivot).Range.CurrentRegion.Cells(2, 1).Value
ActiveSheet.Name = newSheetName
End Sub

[@dominicowusu9330]

Thank you for the video.
I keep getting Run-Time error 1004:
copy method of worksheet class failed.
I have opened my active workbook only.
What could be the cause and solution?

[@ExcelMacroMania]

And you are using the exact code in the video or post? Check the post : excelmacroclass.blogspot.com/2021/09/separate-sheets-into-workbooks.html
Then it's weird, no idea why is that. Try adding ws.Activate before copying the worksheet (ws) maybe.

[@user-oh6es2wb7n]

same problem, did you fix it?

[@manukv1000]

Done ..but one sheet is showing error code 1004.... Can u pls advise,🤔

[@ExcelMacroMania]

That's probably because there is a workbook open with the same name. If there are workbooks with the same name in the target folder, it usually prompts a warning asking if you want to replace the file.

[@manukv1000]

U r amazing.....If we want to save to specific folder .... Pls show the code

[@ExcelMacroMania]

In the code of the video just add the path along with the file to the Filename attribute, for example:
.SaveAs Filename:="C:\Users\Username\Documents\" & ws.name
The code in the post is more self-explanatory, see here:
excelmacroclass.blogspot.com/2021/09/separate-sheets-into-workbooks.html
In that case you just change the value of the variable wbPath (with or without the last backslash, if you concatenate it within the Filename attribute):
wbPath = "C:\Users\Username\Documents"

[@abhilashsharma1645]

Sir where to give path of network where files are saved?

[@ExcelMacroMania]

ActiveWorkbook.SaveAs Filename:="C:\Users\username\documents... \filename.xlsx"

[@DonnyDump]

Did not save as xls files

[@ExcelMacroMania]

Should save by default as .xlsx, but if it doesn't, try adding the extension as follows:
ActiveWorkbook.SaveAs Filename:=ws.Name & ".xlsx" '(or ".xls" but that's old)
or to have it in the same folder:
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ws.Name & ".xlsx"
...

[@helinahill285]

@@ExcelMacroMania thanks for the video and above comment. Is there any way to keep the existing Conditional Formatting after file is split? Thanks in advance.

[@ExcelMacroMania]

@@helinahill285 Well, the code copies everything (all formatting included) into each new workbook. I have just tested it and it keeps the conditional formatting for me (I tried with Highlight cells rules and color scales). Not sure what kind of formatting you have ... or why it does not keep it, so maybe you can try PasteSpecial.
ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats,
but Paste:=xlPasteAll should also work. Or if you want to re-create the conditional formatting, or rather add conditional formatting automatically, check out this other video: ruclips.net/video/8t5OdFdAUng/видео.htmlsi=KnDKdHHWn-SqYWlm

[@helinahill285]

@@ExcelMacroManiathanks. 👍