rav_rav
asked on
Copy Multiple Worksheets to Separate Workbooks VBA
Hi There,
Is there VBA code that you can provide that would allow me to loop through and save multiple worksheets as separate workbooks?
The easiest way I could think of was to define separate arrays, and then write separate code to save each array of worksheets as a separate workbook. What I'm wondering is how to create a nested array and loop through that, where each subarray would have a list of the specific sheet names that I want to save as a different workbook.
Sub CopyWorksheet()
Dim wbNew As Workbook
Dim strFilename As String
Dim ArrayOne As Sheets
Dim ArrayTwo As Sheets
Set ArrayOne = ActiveWorkbook.Sheets(Arra y("Sheet1" , "Sheet3"))
ArrayOne.Copy
strFilename = ArrayOne(1).Name
Set wbNew = ActiveWorkbook
wbNew.SaveAs ThisWorkbook.Path & Application.PathSeparator & strFilename
wbNew.Close SaveChanges:=False
Set ArrayTwo = ActiveWorkbook.Sheets(Arra y("Sheet1" , "Sheet4"))
ArrayTwo.Copy
strFilename = ArrayTwo(1).Name
Set wbNew = ActiveWorkbook
wbNew.SaveAs ThisWorkbook.Path & Application.PathSeparator & strFilename
wbNew.Close SaveChanges:=False
End Sub
Is there VBA code that you can provide that would allow me to loop through and save multiple worksheets as separate workbooks?
The easiest way I could think of was to define separate arrays, and then write separate code to save each array of worksheets as a separate workbook. What I'm wondering is how to create a nested array and loop through that, where each subarray would have a list of the specific sheet names that I want to save as a different workbook.
Sub CopyWorksheet()
Dim wbNew As Workbook
Dim strFilename As String
Dim ArrayOne As Sheets
Dim ArrayTwo As Sheets
Set ArrayOne = ActiveWorkbook.Sheets(Arra
ArrayOne.Copy
strFilename = ArrayOne(1).Name
Set wbNew = ActiveWorkbook
wbNew.SaveAs ThisWorkbook.Path & Application.PathSeparator & strFilename
wbNew.Close SaveChanges:=False
Set ArrayTwo = ActiveWorkbook.Sheets(Arra
ArrayTwo.Copy
strFilename = ArrayTwo(1).Name
Set wbNew = ActiveWorkbook
wbNew.SaveAs ThisWorkbook.Path & Application.PathSeparator & strFilename
wbNew.Close SaveChanges:=False
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.