Automate the import of Data from multiple worksheets into an Excel VBA workbook

The goal of this code is to import data from several sheets of a source workbook into a target workbook.

Objective of the Code

The purpose of the code is to:

1. Open a source workbook.
2. Read data from multiple worksheets in the source workbook.
3. Paste the data into a specific worksheet in the target workbook.

Step 1: Prepare the Target Workbook

Before running the code, ensure you have a target workbook with an empty sheet where the imported data will be pasted.

Step 2: Detailed VBA Code

Here’s a detailed VBA code to automate data import from multiple sheets of a source workbook:

Code Explanation:

• The code assumes you have a source workbook with multiple sheets containing the data you want to import.
• It opens the source workbook, loops through each sheet, and copies the data into a target workbook.
• The code will start pasting the data at the first empty row in the target sheet.

Sub ImportData()
    ' Declare the necessary variables
    Dim SourceWorkbook As Workbook
    Dim SourceSheet As Worksheet
    Dim TargetWorkbook As Workbook
    Dim TargetSheet As Worksheet
    Dim LastRow As Long
    Dim SourceRange As Range
    Dim SourcePath As String
    Dim i As Integer   
    ' Path of the source workbook to import
    SourcePath = "C:\path\to\your\source_file.xlsx"   
    ' Open the target workbook (the current workbook where you are working)
    Set TargetWorkbook = ThisWorkbook
    Set TargetSheet = TargetWorkbook.Sheets("TargetSheet") ' Name of the target sheet where data will be pasted   
    ' Open the source workbook
    Set SourceWorkbook = Workbooks.Open(SourcePath)   
    ' Initialize the starting row in the target workbook
    LastRow = TargetSheet.Cells(TargetSheet.Rows.Count, 1).End(xlUp).Row + 1 ' Find the first empty row in column 1 (A)  
    ' Loop through each sheet in the source workbook
    For Each SourceSheet In SourceWorkbook.Sheets       
        ' Select the data range from the source sheet
        ' Assuming you want to copy all data from A1 to the last used cell
        Set SourceRange = SourceSheet.UsedRange       
        ' Check if the range is not empty
        If Not SourceRange Is Nothing Then
            ' Copy the data from the source sheet
            SourceRange.Copy           
            ' Paste the data into the target sheet starting from the empty row found
            TargetSheet.Cells(LastRow, 1).PasteSpecial Paste:=xlPasteValues            
            ' Update the last row in the target sheet after each paste
            LastRow = TargetSheet.Cells(TargetSheet.Rows.Count, 1).End(xlUp).Row + 1
        End If       
    Next SourceSheet   
    ' Close the source workbook without saving changes
    SourceWorkbook.Close SaveChanges:=False   
    ' Display a message when done
    MsgBox "Import completed!"  
End Sub

Detailed Explanation of the Code:

1. Declaring Variables:
   • SourceWorkbook and SourceSheet represent the source workbook and the individual sheets in it.
   • TargetWorkbook and TargetSheet represent the target workbook and the target sheet where data will be pasted.
   • LastRow is used to find the first empty row in the target sheet to paste data.
   • SourceRange holds the range of data to be copied from the source sheet.
2. Source File Path:
   • The SourcePath variable contains the full path to the source workbook. Replace « C:\path\to\your\source_file.xlsx » with the actual path to your source file.
3. Opening Workbooks:
   • The code opens the target workbook using ThisWorkbook, which refers to the workbook where the VBA code is running.
   • The source workbook is opened using Workbooks.Open(SourcePath).
4. Looping Through Source Sheets:
   • The code loops through each sheet in the source workbook using For Each SourceSheet In SourceWorkbook.Sheets.
   • For each sheet, it defines the SourceRange using UsedRange, which selects all the used cells in the sheet.
5. Copying and Pasting Data:
   • The data from the source sheet is copied and pasted into the target sheet starting from the first empty row.
   • PasteSpecial Paste:=xlPasteValues is used to paste only the values (without formatting or formulas).
6. Updating Last Row:
   • After each paste, the LastRow variable is updated to reflect the new row where the data will be pasted next.
7. Closing the Source Workbook:
   • After all sheets are processed, the source workbook is closed without saving any changes using SourceWorkbook.Close SaveChanges:=False.
8. Completion Message:
   • Once the import is complete, a message box pops up informing the user that the import is done.

Conclusion:

This code automates the process of importing data from multiple sheets in a source workbook into a target workbook. You can customize it further for your specific needs, such as selecting specific ranges, applying filters, or performing transformations before pasting the data into the target sheet.