Categories
Excel

Automate Copying Data Across Workbooks with VBA

This tutorial explains, how to automate data copying across multiple workbooks in Excel using VBA. Imagine you have several workbooks, and each one contains specific data that you want to consolidate into a single sheet. Instead of manually opening each file and copying the data, we’ll use a simple VBA macro to do this in one go!

We’ll start by setting up our VBA environment. First, make sure you have the Developer tab enabled in Excel. If you don’t see it, just go to File > Options > Customize Ribbon and check the ‘Developer’ box.

Next, open the VBA editor by clicking on the Developer tab and selecting Visual Basic. Now, in the VBA editor, create a new module where we’ll write our macro. To do this, go to Insert > Module.

Let’s write the macro that will allow us to select multiple workbooks, copy the range A2:D2 (single row) from each workbook, and paste it into the active sheet.

Here’s the code:

Sub CopyRangeFromSelectedWorkbooks()
 Dim wb As Workbook
 Dim sourceRange As Range
 Dim destRange As Range
 Dim selectedFiles As Variant
 Dim lastRow As Long
 Dim activeSheet As Worksheet

 ' Set active sheet
 Set activeSheet = ThisWorkbook.ActiveSheet

 ' Ask user to select workbooks
 selectedFiles = Application.GetOpenFilename( _
                 FileFilter:="Excel Files (*.xls; *.xlsx), *.xls; *.xlsx", _
                 MultiSelect:=True, Title:="Select Workbooks")

  ' Check if the user selected any files
  If IsArray(selectedFiles) Then
   For i = LBound(selectedFiles) To UBound(selectedFiles)
    ' Open each selected workbook
    Set wb = Workbooks.Open(selectedFiles(i))

    ' Set source range in each workbook
    Set sourceRange = wb.Sheets(1).Range("A2:D2")

    ' Find the next empty row in the destination (active) sheet
    lastRow = activeSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    Set destRange = activeSheet.Range("A" & lastRow & ":D" & lastRow)

    ' Copy source range to destination range in active sheet
    sourceRange.Copy destRange

    ' Close the source workbook without saving changes
    wb.Close False
   Next i
  Else
   MsgBox "No files selected."
  End If
End Sub

This macro asks the user to select multiple workbooks. It then opens each workbook, copies the data from A2:D2, and pastes it into the active sheet starting at the first available row.

Each time the macro finds the last row in the active sheet, it pastes the data starting from that row and moves down. This ensures that we’re appending the data from all selected workbooks without overwriting any existing information.

To test this macro, simply save your workbook as a macro-enabled file by going to File > Save As (or press CTRL + S) and selecting Excel Macro-Enabled Workbook. Now, press Alt + F8, choose the macro CopyRangeFromSelectedWorkbooks, and run it.

Select a few workbooks that contain data in A2:D2 and watch the magic happen! The data will be copied and pasted into the active sheet, starting at A2.

And that’s it! You’ve just learned how to automate the process of copying specific ranges from multiple workbooks into the active sheet in Excel. This can save you a lot of time and effort, especially when handling large volumes of data.