Saves Individual Worksheets as Workbooks in destination folder











up vote
0
down vote

favorite












This code takes, on average, 6 minutes to fully run with the workbook I am using. The workbook currently has 148 worksheets and will grow consistently with time.



I am curious if there is a way to edit the code from doing the task one worksheet at a time; and, instead complete the task all in one moment. In my mind, the code would run for a much shorter time and then populate all the worksheets as separate workbooks in the file destination at the same time. The worksheets would also not "pop-up" before saving themselves in the destination folder. Much like stopping screen updating and automatic calculations making a world of difference - except that did not work with this code.



Sub SaveFilesInFolder()
'
'Macro for saving each worksheet as a workbook in a destination folder
'

'
Dim sh As Worksheet
Dim wb As Workbook

For Each sh In Worksheets
SheetName = sh.Name
sh.Copy

With ActiveWorkbook
.SaveAs FileName:="FolderDestination" & SheetName
.Close SaveChanges:=True
End With

Next sh

End Sub


Any suggestions are appreciated.










share|improve this question














bumped to the homepage by Community 11 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.















  • You declare wb but do not use it. Perhaps this provides a clue.
    – AJD
    Mar 12 at 18:51










  • Just an aside question. Why on earth would anyone think it is a good idea to make a 148sheet workbook?
    – Maarten Fabré
    Oct 9 at 12:48















up vote
0
down vote

favorite












This code takes, on average, 6 minutes to fully run with the workbook I am using. The workbook currently has 148 worksheets and will grow consistently with time.



I am curious if there is a way to edit the code from doing the task one worksheet at a time; and, instead complete the task all in one moment. In my mind, the code would run for a much shorter time and then populate all the worksheets as separate workbooks in the file destination at the same time. The worksheets would also not "pop-up" before saving themselves in the destination folder. Much like stopping screen updating and automatic calculations making a world of difference - except that did not work with this code.



Sub SaveFilesInFolder()
'
'Macro for saving each worksheet as a workbook in a destination folder
'

'
Dim sh As Worksheet
Dim wb As Workbook

For Each sh In Worksheets
SheetName = sh.Name
sh.Copy

With ActiveWorkbook
.SaveAs FileName:="FolderDestination" & SheetName
.Close SaveChanges:=True
End With

Next sh

End Sub


Any suggestions are appreciated.










share|improve this question














bumped to the homepage by Community 11 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.















  • You declare wb but do not use it. Perhaps this provides a clue.
    – AJD
    Mar 12 at 18:51










  • Just an aside question. Why on earth would anyone think it is a good idea to make a 148sheet workbook?
    – Maarten Fabré
    Oct 9 at 12:48













up vote
0
down vote

favorite









up vote
0
down vote

favorite











This code takes, on average, 6 minutes to fully run with the workbook I am using. The workbook currently has 148 worksheets and will grow consistently with time.



I am curious if there is a way to edit the code from doing the task one worksheet at a time; and, instead complete the task all in one moment. In my mind, the code would run for a much shorter time and then populate all the worksheets as separate workbooks in the file destination at the same time. The worksheets would also not "pop-up" before saving themselves in the destination folder. Much like stopping screen updating and automatic calculations making a world of difference - except that did not work with this code.



Sub SaveFilesInFolder()
'
'Macro for saving each worksheet as a workbook in a destination folder
'

'
Dim sh As Worksheet
Dim wb As Workbook

For Each sh In Worksheets
SheetName = sh.Name
sh.Copy

With ActiveWorkbook
.SaveAs FileName:="FolderDestination" & SheetName
.Close SaveChanges:=True
End With

Next sh

End Sub


Any suggestions are appreciated.










share|improve this question













This code takes, on average, 6 minutes to fully run with the workbook I am using. The workbook currently has 148 worksheets and will grow consistently with time.



I am curious if there is a way to edit the code from doing the task one worksheet at a time; and, instead complete the task all in one moment. In my mind, the code would run for a much shorter time and then populate all the worksheets as separate workbooks in the file destination at the same time. The worksheets would also not "pop-up" before saving themselves in the destination folder. Much like stopping screen updating and automatic calculations making a world of difference - except that did not work with this code.



Sub SaveFilesInFolder()
'
'Macro for saving each worksheet as a workbook in a destination folder
'

'
Dim sh As Worksheet
Dim wb As Workbook

For Each sh In Worksheets
SheetName = sh.Name
sh.Copy

With ActiveWorkbook
.SaveAs FileName:="FolderDestination" & SheetName
.Close SaveChanges:=True
End With

Next sh

End Sub


Any suggestions are appreciated.







vba excel






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Mar 12 at 18:42









Sean Pakulski

1




1





bumped to the homepage by Community 11 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 11 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.














  • You declare wb but do not use it. Perhaps this provides a clue.
    – AJD
    Mar 12 at 18:51










  • Just an aside question. Why on earth would anyone think it is a good idea to make a 148sheet workbook?
    – Maarten Fabré
    Oct 9 at 12:48


















  • You declare wb but do not use it. Perhaps this provides a clue.
    – AJD
    Mar 12 at 18:51










  • Just an aside question. Why on earth would anyone think it is a good idea to make a 148sheet workbook?
    – Maarten Fabré
    Oct 9 at 12:48
















You declare wb but do not use it. Perhaps this provides a clue.
– AJD
Mar 12 at 18:51




You declare wb but do not use it. Perhaps this provides a clue.
– AJD
Mar 12 at 18:51












Just an aside question. Why on earth would anyone think it is a good idea to make a 148sheet workbook?
– Maarten Fabré
Oct 9 at 12:48




Just an aside question. Why on earth would anyone think it is a good idea to make a 148sheet workbook?
– Maarten Fabré
Oct 9 at 12:48










1 Answer
1






active

oldest

votes

















up vote
0
down vote













What you're looking for is to turn off screenupdating, turn off alerts and auto-save and close.



    Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close False


So, like this -



Option Explicit

Sub SplitSheetsToBooks()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Const PATH_TO As String = "C:TEMP"
Const EXCEL_EXTENTION As String = ".xlsx"

Dim index As Long
Dim numberOfSheets As Long
Dim targetSheet As Worksheet
numberOfSheets = ThisWorkbook.Sheets.Count

For index = 1 To numberOfSheets
Set targetSheet = ThisWorkbook.Sheets(index)
targetSheet.Copy
ActiveWorkbook.SaveAs PATH_TO & targetSheet.Name & EXCEL_EXTENTION
ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub





share|improve this answer





















  • The above code works exactly how I imagined; however, there is an issue I cannot figure out. The files do not follow the PATH_TO, they instead save to my desktop with the filename "TestSheet1". I have no idea why and I can't see anything in the code that would be causing this.
    – Sean Pakulski
    Mar 13 at 14:56











Your Answer





StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "196"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f189427%2fsaves-individual-worksheets-as-workbooks-in-destination-folder%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes








up vote
0
down vote













What you're looking for is to turn off screenupdating, turn off alerts and auto-save and close.



    Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close False


So, like this -



Option Explicit

Sub SplitSheetsToBooks()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Const PATH_TO As String = "C:TEMP"
Const EXCEL_EXTENTION As String = ".xlsx"

Dim index As Long
Dim numberOfSheets As Long
Dim targetSheet As Worksheet
numberOfSheets = ThisWorkbook.Sheets.Count

For index = 1 To numberOfSheets
Set targetSheet = ThisWorkbook.Sheets(index)
targetSheet.Copy
ActiveWorkbook.SaveAs PATH_TO & targetSheet.Name & EXCEL_EXTENTION
ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub





share|improve this answer





















  • The above code works exactly how I imagined; however, there is an issue I cannot figure out. The files do not follow the PATH_TO, they instead save to my desktop with the filename "TestSheet1". I have no idea why and I can't see anything in the code that would be causing this.
    – Sean Pakulski
    Mar 13 at 14:56















up vote
0
down vote













What you're looking for is to turn off screenupdating, turn off alerts and auto-save and close.



    Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close False


So, like this -



Option Explicit

Sub SplitSheetsToBooks()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Const PATH_TO As String = "C:TEMP"
Const EXCEL_EXTENTION As String = ".xlsx"

Dim index As Long
Dim numberOfSheets As Long
Dim targetSheet As Worksheet
numberOfSheets = ThisWorkbook.Sheets.Count

For index = 1 To numberOfSheets
Set targetSheet = ThisWorkbook.Sheets(index)
targetSheet.Copy
ActiveWorkbook.SaveAs PATH_TO & targetSheet.Name & EXCEL_EXTENTION
ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub





share|improve this answer





















  • The above code works exactly how I imagined; however, there is an issue I cannot figure out. The files do not follow the PATH_TO, they instead save to my desktop with the filename "TestSheet1". I have no idea why and I can't see anything in the code that would be causing this.
    – Sean Pakulski
    Mar 13 at 14:56













up vote
0
down vote










up vote
0
down vote









What you're looking for is to turn off screenupdating, turn off alerts and auto-save and close.



    Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close False


So, like this -



Option Explicit

Sub SplitSheetsToBooks()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Const PATH_TO As String = "C:TEMP"
Const EXCEL_EXTENTION As String = ".xlsx"

Dim index As Long
Dim numberOfSheets As Long
Dim targetSheet As Worksheet
numberOfSheets = ThisWorkbook.Sheets.Count

For index = 1 To numberOfSheets
Set targetSheet = ThisWorkbook.Sheets(index)
targetSheet.Copy
ActiveWorkbook.SaveAs PATH_TO & targetSheet.Name & EXCEL_EXTENTION
ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub





share|improve this answer












What you're looking for is to turn off screenupdating, turn off alerts and auto-save and close.



    Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close False


So, like this -



Option Explicit

Sub SplitSheetsToBooks()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Const PATH_TO As String = "C:TEMP"
Const EXCEL_EXTENTION As String = ".xlsx"

Dim index As Long
Dim numberOfSheets As Long
Dim targetSheet As Worksheet
numberOfSheets = ThisWorkbook.Sheets.Count

For index = 1 To numberOfSheets
Set targetSheet = ThisWorkbook.Sheets(index)
targetSheet.Copy
ActiveWorkbook.SaveAs PATH_TO & targetSheet.Name & EXCEL_EXTENTION
ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub






share|improve this answer












share|improve this answer



share|improve this answer










answered Mar 12 at 23:23









Raystafarian

5,7941048




5,7941048












  • The above code works exactly how I imagined; however, there is an issue I cannot figure out. The files do not follow the PATH_TO, they instead save to my desktop with the filename "TestSheet1". I have no idea why and I can't see anything in the code that would be causing this.
    – Sean Pakulski
    Mar 13 at 14:56


















  • The above code works exactly how I imagined; however, there is an issue I cannot figure out. The files do not follow the PATH_TO, they instead save to my desktop with the filename "TestSheet1". I have no idea why and I can't see anything in the code that would be causing this.
    – Sean Pakulski
    Mar 13 at 14:56
















The above code works exactly how I imagined; however, there is an issue I cannot figure out. The files do not follow the PATH_TO, they instead save to my desktop with the filename "TestSheet1". I have no idea why and I can't see anything in the code that would be causing this.
– Sean Pakulski
Mar 13 at 14:56




The above code works exactly how I imagined; however, there is an issue I cannot figure out. The files do not follow the PATH_TO, they instead save to my desktop with the filename "TestSheet1". I have no idea why and I can't see anything in the code that would be causing this.
– Sean Pakulski
Mar 13 at 14:56


















draft saved

draft discarded




















































Thanks for contributing an answer to Code Review Stack Exchange!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


Use MathJax to format equations. MathJax reference.


To learn more, see our tips on writing great answers.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f189427%2fsaves-individual-worksheets-as-workbooks-in-destination-folder%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

404 Error Contact Form 7 ajax form submitting

How to know if a Active Directory user can login interactively

TypeError: fit_transform() missing 1 required positional argument: 'X'