+ Reply to Thread
Results 1 to 6 of 6

How to copy data from two workbooks and append to another workbook?

Hybrid View

  1. #1
    Valued Forum Contributor
    Join Date
    12-01-2016
    Location
    Planet Earth
    MS-Off Ver
    95 - 2016
    Posts
    343

    Re: How to copy data from two workbooks and append to another workbook?

    I ran this code from a separate macro (.xlsm) enabled workbook

    Try this code
    Sub CombineWBks()
      Dim strFirstFile, strSecondFile, strThirdFile As String
      Dim wbk1, wbk2, wbk3 As Workbook
      
    'Speeding up the macro
        Application.ScreenUpdating = False
        EventState = Application.EnableEvents
        Application.EnableEvents = False
        CalcState = Application.Calculation
        Application.Calculation = xlCalculationManual
        PageBreakState = ActiveSheet.DisplayPageBreaks
        ActiveSheet.DisplayPageBreaks = False
        Application.DisplayAlerts = False
        
    'Change file path to match your files locations
        strFirstFile = "C:\_ExcelForum\CombineWorkbooks\Workbook 1.xlsx"  'Change to your location & ensure the workbook exist
        strSecondFile = "C:\_ExcelForum\CombineWorkbooks\Workbook 2.xlsx" 'Change to your location & ensure the workbook exist
        strThirdFile = "C:\_ExcelForum\CombineWorkbooks\Workbook 3.xlsx" 'Change to your location & ensure the workbook exist
          
          Set wbk1 = Workbooks.Open(strFirstFile)
          Set wbk3 = Workbooks.Open(strThirdFile)
    
    'Clears data within sheet1 of the third file
             'wbk3.Sheets("sheet1").UsedRange.ClearContents 'Clears the third file's data (used only for testing}
             
    'Copy process begins
              wbk1.Sheets("Sheet1").Range("$A:$E").Copy wbk3.Sheets("Sheet1").Range("$A:$E") 'Change the Sheet name and data range accordingly
              wbk1.Close
      
          Set wbk2 = Workbooks.Open(strSecondFile)
    
              wbk2.Sheets("Sheet1").Range("A2:E500000").Copy wbk3.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) 'Change the Sheet name and data range accordingly
          
              wbk3.Save
              wbk3.Close
              
    'Restoring Speeding up the macro
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAuto
        ActiveSheet.DisplayPageBreaks = True
    
    End Sub
    Last edited by Syrkrasi; 10-19-2017 at 04:24 PM.

  2. #2
    Registered User
    Join Date
    04-04-2017
    Location
    Sydney
    MS-Off Ver
    2013
    Posts
    9

    Re: How to copy data from two workbooks and append to another workbook?

    Hi I get a runtime error '9' - Subscript out of range in running the following code for the line (wbk1.Sheets("Sheet1").Range("$A:$K").Copy wbk3.Sheets("Copy").Range("$A:$K") 'Change the Sheet name and data range accordingly)

    Sub CombineWBks()
    Dim strFirstFile, strSecondFile, strThirdFile, dialogTitle As String
    Dim wbk1, wbk2, wbk3 As Workbook
    Dim fileDialog As fileDialog

    'Speeding up the macro
    Application.ScreenUpdating = False
    EventState = Application.EnableEvents
    Application.EnableEvents = False
    CalcState = Application.Calculation
    Application.Calculation = xlCalculationManual
    PageBreakState = ActiveSheet.DisplayPageBreaks
    ActiveSheet.DisplayPageBreaks = False
    Application.DisplayAlerts = False

    dialogTitle = "Navigate to and select required file."
    Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
    'Change file path to match your files locations
    With fileDialog
    .InitialFileName = "C:\Users\IsuruGaj\Downloads"
    '.InitialFileName = ThisWorkbook.Path & "\" 'Alternative to previous line
    .AllowMultiSelect = True
    .Filters.Clear
    .Title = dialogTitle
    If .Show = False Then
    MsgBox "File not selected to import. Process Terminated"
    Exit Sub
    End If
    strFirstFile = .SelectedItems(1)
    strSecondFile = .SelectedItems(2)
    End With

    strThirdFile = "X:\EAPM\Command Centre\Status report\TfNSW CoE - Weekly Remedy Status Report_version4.xlsm"
    Set wbk1 = Workbooks.Open(Filename:=strFirstFile)
    'Set wbk_2 = Workbooks.Open(Filename:=strSecondFile)
    Set wbk3 = Workbooks.Open(strThirdFile)

    ' strFirstFile = "C:\_ExcelForum\CombineWorkbooks\Workbook 1.xlsx" 'Change to your location & ensure the workbook exist
    ' strSecondFile = "C:\_ExcelForum\CombineWorkbooks\Workbook 2.xlsx" 'Change to your location & ensure the workbook exist
    ' strThirdFile = "C:\_ExcelForum\CombineWorkbooks\Workbook 3.xlsx" 'Change to your location & ensure the workbook exist


    'Clears data within sheet1 of the third file
    'wbk3.Sheets("sheet1").UsedRange.ClearContents 'Clears the third file's data (used only for testing}

    'Copy process begins
    wbk1.Sheets("Sheet1").Range("$A:$K").Copy wbk3.Sheets("Copy").Range("$A:$K") 'Change the Sheet name and data range accordingly
    wbk1.Close

    Set wbk2 = Workbooks.Open(Filename:=strSecondFile)

    wbk2.Sheets("Sheet1").Range("$A2:$K").Copy wbk3.Sheets("Copy").Range("A2" & Rows.Count).End(xlUp).Offset(1, 0) 'Change the Sheet name and data range accordingly

    wbk3.Save
    wbk3.Close

    'Restoring Speeding up the macro
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAuto
    ActiveSheet.DisplayPageBreaks = True

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. copy data from several workbooks into one new workbook
    By jojo101 in forum Excel General
    Replies: 1
    Last Post: 12-28-2014, 06:31 AM
  2. A Challenging VBA - Copy data from multiple workbooks into one workbook
    By speed88bump in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-26-2013, 12:29 PM
  3. Replies: 32
    Last Post: 09-16-2013, 01:40 AM
  4. Copy data from several workbooks to one master workbook
    By beginneratexcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2013, 03:24 PM
  5. [SOLVED] Copy Data From Multiple Workbooks to One Workbook
    By danderson2692 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-19-2012, 04:36 PM
  6. Copy Data from Multiple Workbooks into another Workbook
    By trust_lord in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-29-2011, 10:33 PM
  7. copy duplicate data from two workbooks into new workbook
    By getfunky in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-24-2008, 11:11 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1