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:
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

Open in new window

LVL 1
m_travisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byronwallCommented:
Which part is not doing what you expect?  Just looking at the code, you generally do not want to use a For Each to iterate through a collection that you are going to delete from.  It is better to do a simpler

For i = Workbooks(thatWb).Worksheets.Count To 1        
        Dim SH As Worksheet
        Set SH = Workbooks(thatWb).Worksheets(i)
        If InStr(SH.Name, "Sheet") Then
            SH.Delete
        End If
    Next i

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

Application.DisplayAlerts = False

 'saveas code
'
'

Application.DisplayAlerts = True

Open in new window


If something else is not working, mention it specifically.
0
Rob HensonFinance AnalystCommented:
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
0
m_travisAuthor Commented:
It does but i need this done in VBA.
0
Rob HensonFinance AnalystCommented:
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
0
Rob HensonFinance AnalystCommented:
This line will create a new file with only sheets 2 to 4 of the original, change sheet names appropriately:

Sheets(Array("Sheet2", "Sheet3", "Sheet4")).Copy

I tested it with a Sheet5 between 3 & 4 and it still only copied 2, 3 & 4.

Thanks
Rob H
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.