Solved

Create new Workbook, copy sheets over in order, then save as

Posted on 2014-10-29
5
140 Views
Last Modified: 2014-11-13
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

0
Comment
Question by:m_travis
  • 3
5 Comments
 
LVL 3

Expert Comment

by:byronwall
ID: 40411743
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40413242
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
 
LVL 1

Author Comment

by:m_travis
ID: 40413250
It does but i need this done in VBA.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40413259
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
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40413267
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now