Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Create new workbook with tabs from another (VBA)

    Still at my learning curve with VBA.

    I have a workbook with a number of tabs. I need a VBA macro that will create a new workbook which will be named using two cells from the first sheet in the initial workbook. For example, the new file will have the name: "B4" & Q2

    Then, there are 5 tabs (not all of the workbook) that need to be copied and placed in this new file.

    Sounds straight-foward if I weren't still on this learning curve.

    What I've learned so far from this lounge is just wonderful and greatly appreciated.

    /Kevin

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    A little clarification?
    1. The sheet containing the cells B4 & Q2 does it have a name or is it always sheet1 (actually sheet1 not just in the left most position)?
    2. Are the values in B4 & Q2 always Text or could they be a number or date? If so will they consistently be one or the other?
    3. Do the tabs/sheets to be copied have names? If so what are they and If not how does the code know which ones to copy?
    4. Do you want just the Tab Names copied or the entire contents of the tab, i.e. sheet?

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    1. The sheet containing the cells B4 & Q2 does it have a name or is it always sheet1 (actually sheet1 not just in the left most position)?
    Sheet name: NewIncident

    2. Are the values in B4 & Q2 always Text or could they be a number or date? If so will they consistently be one or the other?
    B2 is text and Q2 is a number

    3. Do the tabs/sheets to be copied have names? If so what are they and If not how does the code know which ones to copy?
    Copy:
    NewIncident
    Initial Report (with the space)
    24 Hr Report "
    7 Day Follow-Up Report "
    30 Day Follow-Up "

    4. Do you want just the Tab Names copied or the entire contents of the tab, i.e. sheet?
    Entire contents...i.e., I want to make a separate file under the new name this those 5 new tabs with all of the data from the original.
    Paste Special Values

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    My attempt to get this started:

    Sub copy_sheets()
    Dim wb1 As Workbook
    Dim ws1 As Worksheet
    Dim wb2 As Workbook
    Dim ws2 As Worksheet
    Application.SheetsInNewWorkbook = 1

    Set wb2 = Workbooks.Add

    Set wb1 = Workbooks("NewIncidentB.xlsm")
    Set ws1 = wb1.Sheets("NewIncident")
    wb2.SaveAs Filename:=ws1.Range("B4") & ws1.Range("Q2")

    With wb2
    Set ws2 = .Sheets.Add(After:=.Worksheets(.Worksheets.Count))
    wb1.Sheets("NewIncident").Copy
    End With
    With ws2.Range("A1")
    .PasteSpecial Paste:=xlPasteColumnWidths
    .PasteSpecial Paste:=xlPasteValues
    ws2.Name = "NewIncident"
    End With

    Application.SheetsInNewWorkbook = 3
    End Sub

    The new workbook with the proper name is created, but there's also another workbook created (e.g., Book5) and THAT one has the copy and pasted worksheet. ??

    The other issue I found is that it is not copying/pasting values...merged cells don't seem to be copy/pasted values. They still have formulas in them.

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    Another question. Do any of the copied sheets refer to data on one of the sheets that are NOT copied?
    If so do you want to have them link back to the original sheet or should all data be copied as values?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    No, don't want any links back...just a copy paste special VALUES (including merged cells and those that had formulas)

    I think if I replace the copy with a range to be copied, it does the trick.

    Is this right?

    How do I then copy the headers and footers and paste them into the new workbook's sheets?
    Last edited by kweaver; 2012-07-08 at 20:24.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    I think this code should cover it although it may still need tweaking for questions I forgot to ask.
    Please note this was devloped on Excel 2003 and I think you are using 2010 so you'll need to change the one occurance of .xls to .xlsx also you will want to make the base file containing the macro a .xlsm file and put it in a "Trusted Location".
    Code:
    Option Explicit
    
    Sub CopySheetsToNewWorkbook()
    
       Dim oBaseWkBk    As Workbook
       Dim oNewWkBk     As Workbook
       Dim zSheets(5)   As String
       Dim zNewFileName As String
       Dim iCntr        As Integer
       
       zSheets(0) = "Newincident"
       zSheets(1) = "Initial Report"
       zSheets(2) = "24 Hr Report"
       zSheets(3) = "7 Day Follow-Up Report"
       zSheets(4) = "30 Day Follow-Up Report"
        
        Set oBaseWkBk = ActiveWorkbook
        Sheets("Newincident").Select
        zNewFileName = [B4].Value & Format([Q2].Value) & ".xls"
        Set oNewWkBk = Workbooks.Add
        Application.DisplayAlerts = False
        oNewWkBk.SaveAs Filename:=zNewFileName, _
            FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False
        Application.DisplayAlerts = True
        
        
        For iCntr = 0 To UBound(zSheets) - 1
           oBaseWkBk.Activate
           Sheets(zSheets(iCntr)).Copy Before:=Workbooks(oNewWkBk.Name).Sheets("Sheet1")
           On Error GoTo NoCellsSelected
           Selection.SpecialCells(xlCellTypeFormulas, 23).Select
           With Selection
               .Copy
               .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                      SkipBlanks:=False, Transpose:=False
           End With  'Selection
           Application.CutCopyMode = False
    NoCellsSelected:
           Resume Next
           On Error GoTo 0
        Next iCntr
        
        Application.DisplayAlerts = False
        oNewWkBk.Sheets("Sheet1").Delete
        Application.DisplayAlerts = True
      
    End Sub
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Thanks. I'm learning slowing but surely. VBA is interesting. I have a great deal of experience programming in a high level math language called APL...sure beats Excel and VBA and everything else (APL does), but the world uses Excel...but, thousands of actuaries use APL.

    Again, thanks for your help. Always appreciated!!

    /Kevin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •