[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

Appending one workbook template) to another workbook

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.
Frank Freese
Frank Freese
  • 4
  • 2
1 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

Frank FreeseAuthor Commented:
I'll give it a try later today - thank you
Frank FreeseAuthor Commented:
That is too cool...thank you for a jib well done!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Frank FreeseAuthor Commented:
My pleasure!
Frank FreeseAuthor Commented:
Simply fantastic - BTW, I just posted a follow-up

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now