m_travis
asked on
Create new Workbook, copy sheets over in order, then save as
This is horrible that I can't figure this out with the help of google.
I have a spreadsheet1 with 5 sheets(sheet1, sheet2, etc...). I want to open a new workbook, copy the sheets over in order, delete the original 3 sheets, then save as without a prompt.
This is what I am working with so far:
I have a spreadsheet1 with 5 sheets(sheet1, sheet2, etc...). I want to open a new workbook, copy the sheets over in order, delete the original 3 sheets, then save as without a prompt.
This is what I am working with so far:
Sub SaveOut()
thisWb = ActiveWorkbook.Name
Workbooks.Add
thatWb = ActiveWorkbook.Name
Workbooks(thisWb).Activate
Dim SH As Worksheet
sheetCount = 1
For Each SH In Workbooks(thisWb).Worksheets
If InStr(SH.Name, "Main") Then
Else
SH.Copy Before:=Workbooks(thatWb).Sheets(sheetCount)
sheetCount = sheetCount + 1
End If
Next SH
For Each SH In Workbooks(thatWb).Worksheets
If InStr(SH.Name, "Sheet") Then
SH.Delete
End If
Next SH
End Sub
In the original workbook, if you select only the sheets that you require in the new workbook, you can create a copy of those as a new Workbook rather than opening a new workbook and copying to it. You then don't have the Sheet1, 2, 3 of the New workbook to delete.
Does that make sense?
Thanks
Rob H
Does that make sense?
Thanks
Rob H
ASKER
It does but i need this done in VBA.
The attached screen shot shows the Move or Copy dialogue after Right click on a tab and select "Move or Copy".
As you can see, the dropdown at the top gives the option for New Book.
If you have multiple sheets selected, all selected sheets will be copied. To select multiple sheets either
1) select first and then press Shift before selecting last, assuming all sheets in a group one after the other,
2) select first and then press Ctrl and select each of the others individually, no need for them to be together.
Thanks
Rob H
NewWorkbook.PNG
As you can see, the dropdown at the top gives the option for New Book.
If you have multiple sheets selected, all selected sheets will be copied. To select multiple sheets either
1) select first and then press Shift before selecting last, assuming all sheets in a group one after the other,
2) select first and then press Ctrl and select each of the others individually, no need for them to be together.
Thanks
Rob H
NewWorkbook.PNG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
to delete them. This prevents the collection from changing size mid-loop.
If you don't want the alerts, then use
Open in new window
If something else is not working, mention it specifically.