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
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
@@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
@@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.
@@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
@@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.
@@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
The code to do that would probably look to what was explained in this other video (Copy Columns To Separate Sheets): th-cam.com/video/SLjSWCNW0qk/w-d-xo.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.
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?
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.
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.
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"
The macro will run for the active workbook unless otherwise specified. Learn how to target the active workbook vs ThisWorkbook, or to specifically reference a workbook in this other video: th-cam.com/video/rNqaL-JgBxU/w-d-xo.htmlsi=Cf2MsePgT9OCJA_Z
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" ...
@@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.
@@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: th-cam.com/video/8t5OdFdAUng/w-d-xo.htmlsi=KnDKdHHWn-SqYWlm
Thank you so much
First like from my side
second like from my side 😊 keep uploading such videos
Great Code sir.👌🏻👌🏻
So comfortable and easy to use..
Thanks for the code
Keep uploading such videos
Excellent code, simple and effective.
Q - is it possible to exclude certain sheets from copying rather than copying all sheets?
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
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
@@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
Can u please explain how to export only the selected sheets instead of all the sheets? TIA
Change this in the code: For Each ws In ActiveWindow.SelectedSheets
@@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.
@@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
@@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.
@@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
Hey, do you know how to pull out only columns with specific names (for example only model columns) form each worksheet into separate workbooks?
The code to do that would probably look to what was explained in this other video (Copy Columns To Separate Sheets): th-cam.com/video/SLjSWCNW0qk/w-d-xo.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.
nice
Great ❤ 🔥
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?
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.
same problem, did you fix it?
@@RusselAshleyPascua-q1m I had a similar issue and added ws.Visible = True to the code which worked. Hope this helps
Done ..but one sheet is showing error code 1004.... Can u pls advise,🤔
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.
U r amazing.....If we want to save to specific folder .... Pls show the code
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"
Sir, not able to use this code once I am saving this in module under personal macro workbook and apply for other open workbook. Any solution to this?
The macro will run for the active workbook unless otherwise specified. Learn how to target the active workbook vs ThisWorkbook, or to specifically reference a workbook in this other video: th-cam.com/video/rNqaL-JgBxU/w-d-xo.htmlsi=Cf2MsePgT9OCJA_Z
Sir where to give path of network where files are saved?
ActiveWorkbook.SaveAs Filename:="C:\Users\username\documents... \filename.xlsx"
Did not save as xls files
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"
...
@@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.
@@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: th-cam.com/video/8t5OdFdAUng/w-d-xo.htmlsi=KnDKdHHWn-SqYWlm
@@ExcelMacroManiathanks. 👍