?
Solved

Creating a second workbook from an original workbook

Posted on 2013-01-29
6
Medium Priority
?
325 Views
Last Modified: 2013-01-30
Workbook has 10 tabs. With the use of a macro a new workbook is created with 5 of the tabs.
The new workbook is saved to where the original is with a new name - if that is possible.
Any help greatly appreciated.
0
Comment
Question by:dgd1212
  • 4
  • 2
6 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38833576
Hi, dgd1212.

What version of Excel are you using?
What file type do you want to use (xlsx/xlsm/xlsb/xls)?
Any particular name for the new file?

Thanks,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38833598
dgd1212,

Please see previous post. I've made some assumptions...
 - The new file is saved as an xlsx.
 - Sheets2/4/6/8/10 are copied.
 - The output file is called "Fred.xlsx", unless that already exists, when the file is given the same name as the source file + "_yyyymmdd_hhmmss"

The code is...
Option Explicit

Sub Copy_Sheets()
Dim xBook As Workbook

Application.ScreenUpdating = False
    Sheets(Array("Sheet10", "Sheet8", "Sheet6", "Sheet4", "Sheet2")).Copy
    Set xBook = ActiveWorkbook

    If Dir(ThisWorkbook.Path & "\" & "Fred.xlsx") = "" Then
        xBook.SaveAs Filename:=ThisWorkbook.Path & "\" & ThisWorkbook.Name & "_" & Format(Now(), "yyyymmdd_hhnnss") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Else
        xBook.SaveAs Filename:=ThisWorkbook.Path & "\" & "Fred.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    End If
    
    xBook.Close savechanges:=False

Application.ScreenUpdating = True

End Sub

Open in new window

Regards,
Brian.
Copy-Sheets.xlsm
0
 

Author Comment

by:dgd1212
ID: 38833831
Brian,
I'm using Excel 2010. Type file would be xlsm (for macros?)
I used your attached example spreadsheet. Could not find the created file though. No fred.xlsx.
suggestions?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 38833864
Hi, dgd1212.

Please see attached. I've made a few changes...
 - Now saves as xlsm.
 - Corrected the check for an existing file.
 - Displays a message with the name of the file just saved.

The code is...
Option Explicit

Sub Copy_Sheets()
Dim xBook As Workbook
Dim xFileName As String

Application.ScreenUpdating = False
    Sheets(Array("Sheet10", "Sheet8", "Sheet6", "Sheet4", "Sheet2")).Copy
    Set xBook = ActiveWorkbook

    If Dir(ThisWorkbook.Path & "\" & "Fred.xlsm") <> "" Then
        xFileName = ThisWorkbook.Path & "\" & ThisWorkbook.Name & "_" & Format(Now(), "yyyymmdd_hhnnss") & ".xlsm"
        xBook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    Else
        xFileName = ThisWorkbook.Path & "\" & "Fred.xlsm"
        xBook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End If

    xBook.Close savechanges:=False

Application.ScreenUpdating = True

MsgBox ("Sheets saved to " & xFileName)

End Sub

Open in new window

Regards,
Brian.Copy-Sheets-V2.xlsm
0
 

Author Closing Comment

by:dgd1212
ID: 38835387
Brian,
Thank you!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38835654
Glad to help, dgd1212!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

601 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