Appending one workbook template) to another workbook

Posted on 2014-07-13
Last Modified: 2014-07-14
I'm not sure how far I can go on this project so I'll take it step-by-step and question by question.
I have a client who tracks costs by project - each project has its own workbook with a tab for Consolidate and 12 tabs for each month.
Projects can go over from one year to another. When that happens they would like to append a template at the end of the  current workbook for the next year.
I have attached two workbooks - the one labeled Project Cost Allocation that will be appended at the end by the file labeled Appending Workbook (this is not a template file at this time.)
What I'm hoping is for a macro labeled Append for the user to click on. If I can get that far I'll open another question on what to do with the appended file to the current workbook.
Question by:Frank Freese
    LVL 26

    Accepted Solution

    Try this:
    Sub Append()
        Dim strFileSelected As String
        Dim objOfficeDialog As Object
        Dim wbDestination As Workbook
        Dim wbSource As Workbook
        Dim sh As Worksheet
        Application.DisplayAlerts = False
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Set objOfficeDialog = Application.FileDialog(msoFileDialogFilePicker)
        Set wbDestination = ActiveWorkbook
        With objOfficeDialog
            .Title = "Select the Project Cost Allocation file"
            .AllowMultiSelect = False
            If .Show <> -1 Then
                Exit Sub
            End If
            strFileSelected = .SelectedItems(1)
        End With
        If strFileSelected <> "" Then
            Set wbSource = Workbooks.Open(strFileSelected)
            For Each sh In wbSource.Sheets
                sh.Copy After:=wbDestination.Worksheets(wbDestination.Worksheets.Count)
            wbSource.Close False
        End If
        Application.DisplayAlerts = True
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        MsgBox "Done"
    End Sub

    Open in new window


    Author Comment

    by:Frank Freese
    I'll give it a try later today - thank you

    Author Comment

    by:Frank Freese
    That is too cool...thank you for a jib well done!

    Author Closing Comment

    by:Frank Freese
    LVL 26

    Expert Comment

    My pleasure!

    Author Comment

    by:Frank Freese
    Simply fantastic - BTW, I just posted a follow-up

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Copy paste as values 4 33
    Opening Files From Excel WB 8 31
    Posting V12 2 16
    Check version 13 45
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    732 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

    17 Experts available now in Live!

    Get 1:1 Help Now