Étiquette : automatisation

The code will perform several common formatting tasks such as:

1. Adjusting column widths.
2. Making headers bold.
3. Applying borders to cells.
4. Changing background color of cells.
5. Aligning cell text.

Goal:

This code formats data in an Excel sheet based on these criteria.

VBA Code for Automating Formatting:

Sub AutomateFormatting()
    ' Declare variables
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim dataRange As Range   
    ' Reference to the active sheet
    Set ws = ThisWorkbook.ActiveSheet   
    ' Find the last row and column with data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastColumn = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column   
    ' Define the data range
    Set dataRange = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn))   
    ' 1. Autofit column widths
    ws.Cells.EntireColumn.AutoFit   
    ' 2. Make headers (row 1) bold
    ws.Rows(1).Font.Bold = True  
    ' 3. Apply borders to the cells
    With dataRange.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With   
    With dataRange.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With   
    ' 4. Apply background color to the first row (headers)
    ws.Rows(1).Interior.Color = RGB(221, 235, 247) ' Light blue color   
    ' 5. Align text in cells
    With dataRange
        .HorizontalAlignment = xlCenter ' Center horizontally
        .VerticalAlignment = xlCenter ' Center vertically
    End With   
    ' 6. Apply number format (e.g., monetary format) to columns
    Dim i As Integer
    For i = 1 To lastColumn
        If IsNumeric(ws.Cells(2, i).Value) Then
            ws.Columns(i).NumberFormat = "#,##0.00" ' Monetary format
        End If
    Next i   
    ' 7. Change the color of negative values to red
    Dim cell As Range
    For Each cell In dataRange
        If IsNumeric(cell.Value) And cell.Value < 0 Then
            cell.Font.Color = RGB(255, 0, 0) ' Red for negative values
        End If
    Next cell   
    ' 8. Add a thick outer border around the data range
    With dataRange.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With   
    With dataRange.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With   
    With dataRange.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With  
    With dataRange.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
End Sub

Detailed Explanation of the Code:

1. Referencing the Active Sheet:
   • Set ws = ThisWorkbook.ActiveSheet assigns ws as the currently active sheet in the workbook.
2. Finding the Last Row and Column with Data:
   • lastRow and lastColumn are used to find the last row with data in the first column and the last column with data in the first row, respectively.
3. Autofitting Column Widths:
   • ws.Cells.EntireColumn.AutoFit automatically adjusts the width of all columns based on the content in each cell.
4. Making the Header Row Bold (Row 1):
   • ws.Rows(1).Font.Bold = True applies bold formatting to the first row (headers).
5. Applying Borders to the Cells:
   • With dataRange.Borders(xlEdgeBottom) and With dataRange.Borders(xlEdgeRight) apply bottom and right borders to each cell in the dataRange.
6. Changing the Background Color of the First Row:
   • ws.Rows(1).Interior.Color = RGB(221, 235, 247) changes the background color of the header row to a light blue.
7. Aligning Text in Cells:
   • dataRange.HorizontalAlignment = xlCenter and dataRange.VerticalAlignment = xlCenter ensure that text in all cells within dataRange is centered both horizontally and vertically.
8. Applying a Number Format (Monetary Format):
   • The code checks if the cell contains a numeric value and applies a monetary format (#,##0.00).
9. Changing Negative Values to Red:
   • If a cell contains a numeric value that is less than zero, the font color of the cell is changed to red.
10. Adding a Thick Outer Border Around the Data Range:
    • The code adds thick borders around the entire data range using xlEdgeTop, xlEdgeLeft, xlEdgeBottom, and xlEdgeRight.

Conclusion:

This code automates several common formatting tasks in Excel. You can customize this code further based on your specific needs, such as modifying colors, number formats, or applying conditions to the formatting. To use the code, just insert it into a VBA module in your Excel workbook, and run it.

Automating data quality checks in Excel using VBA (Visual Basic for Applications) can be very useful for ensuring the integrity and reliability of your data. Below is an example of detailed VBA code to perform several common data quality checks such as:

1. Checking for duplicates in a column.
2. Checking for missing values (empty cells).
3. Checking if values conform to a specific format (e.g., dates or numbers).
4. Checking if values are within a specific range (e.g., scores should not be below 0 or above 100).

Example VBA Code for Automating Data Quality Checks

Sub DataQualityControl()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim col As Long
    Dim cell As Range
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    ' Set the worksheet to be analyzed
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Find the last used row in column A (adjust this as per the column being used)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row   
    ' 1. Check for duplicates in column A
    MsgBox "Checking for duplicates in column A..."
    For i = 2 To lastRow ' Assuming data starts from row 2
        If dict.exists(ws.Cells(i, 1).Value) Then
            ws.Cells(i, 1).Interior.Color = RGB(255, 0, 0) ' Highlight duplicates in red
        Else
            dict.Add ws.Cells(i, 1).Value, Nothing
        End If
    Next i
    ' 2. Check for missing values in column B
    MsgBox "Checking for missing values in column B..."
    For i = 2 To lastRow
        If IsEmpty(ws.Cells(i, 2).Value) Then
            ws.Cells(i, 2).Interior.Color = RGB(255, 255, 0) ' Highlight missing values in yellow
        End If
    Next i
    ' 3. Check for date format in column C
    MsgBox "Checking for date format in column C..."
    For i = 2 To lastRow
        If Not IsDate(ws.Cells(i, 3).Value) And ws.Cells(i, 3).Value <> "" Then
            ws.Cells(i, 3).Interior.Color = RGB(255, 165, 0) ' Highlight invalid date format in orange
        End If
    Next i
    ' 4. Check for range of values in column D (e.g., scores between 0 and 100)
    MsgBox "Checking for valid range in column D..."
    For i = 2 To lastRow
        If IsNumeric(ws.Cells(i, 4).Value) Then
            If ws.Cells(i, 4).Value < 0 Or ws.Cells(i, 4).Value > 100 Then
                ws.Cells(i, 4).Interior.Color = RGB(0, 255, 0) ' Highlight out-of-range values in green
            End If
        End If
    Next i   
    MsgBox "Data quality checks are complete."
End Sub

Explanation of the Code

1. Initialization and Worksheet Setup:
   • The first step is to define the worksheet to be analyzed (in this case, « Sheet1 »).
   • The lastRow variable is used to find the last used row in column A to loop through the data.
2. Duplicate Check in Column A:
   • A Scripting.Dictionary is used to track unique values in column A.
   • If a value appears more than once, it’s considered a duplicate, and the corresponding cell is highlighted in red.
3. Missing Values Check (Empty Cells) in Column B:
   • The code loops through each cell in column B and checks if the cell is empty (IsEmpty).
   • If a cell is empty, it’s highlighted in yellow.
4. Date Format Check in Column C:
   • The code checks whether the values in column C are valid dates using the IsDate function.
   • If a cell contains a value that is not a valid date, it is highlighted in orange.
5. Range Check in Column D:
   • The code checks if the values in column D are numeric and whether they fall within the range 0 to 100.
   • If the value is out of range, the corresponding cell is highlighted in green.
6. Message Boxes:
   • Message boxes are shown before each check to inform the user of the ongoing quality checks.

Possible Improvements

• Customization: You can customize this code based on your data’s specific requirements. For instance, you can add additional checks for other types of data quality issues.
• Summary Report: It might be useful to generate a summary report at the end of the checks, detailing the errors or issues found in the data.

How to Use This Code

1. Open Excel.
2. Press Alt + F11 to open the VBA editor.
3. In the VBA editor, click on Insert > Module.
4. Copy and paste the code above into the module.
5. Close the VBA editor.
6. To run the code, go to the « Developer » tab in Excel, click on « Macros, » select DataQualityControl, and click « Run. »

This code serves as a starting point for automating data quality checks in Excel using VBA. You can modify it further to suit your specific needs.

Automating data analysis processes in Excel using VBA (Visual Basic for Applications) can significantly speed up your workflow. Below is a detailed example of VBA code to automate common data analysis tasks such as reading data, cleaning, generating summary statistics, and performing calculations.

Scenario:

Imagine you have a dataset in an Excel sheet with multiple columns, and you want to:

1. Clean the data (e.g., remove empty rows or duplicates).
2. Calculate basic statistics (mean, sum, standard deviation) for specific columns.
3. Generate a summary with these statistics in a new sheet.

Here is the detailed VBA code with explanations.

Detailed VBA Code:

1. Open the VBA Editor:
   • Press Alt + F11 to open the VBA editor in Excel.
   • Click Insert > Module to create a new module.
2. VBA Code:

Sub AnalyzeData()
    ' Declare variables
    Dim wsData As Worksheet
    Dim wsSummary As Worksheet
    Dim lastRow As Long
    Dim rng As Range
    Dim mean As Double, total As Double, stdDev As Double
    Dim cell As Range   
    ' Set the worksheet containing the data (change the name according to your file)
    Set wsData = ThisWorkbook.Sheets("Data") ' Replace "Data" with your actual data sheet name   
    ' Add a new worksheet for the analysis summary
    On Error Resume Next ' If the sheet already exists, ignore the error
    Set wsSummary = ThisWorkbook.Sheets("Summary")
    On Error GoTo 0 ' Reset error handling   
    If wsSummary Is Nothing Then
        Set wsSummary = ThisWorkbook.Sheets.Add
        wsSummary.Name = "Summary"
    End If   
    ' Find the last used row in the data sheet
    lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row   
    ' Clean the data: Remove empty rows
    Set rng = wsData.Range("A1:A" & lastRow)
    For Each cell In rng
        If IsEmpty(cell.Value) Then
            cell.EntireRow.Delete
        End If
    Next cell   
    ' Find the last row again after cleaning
    lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row   
    ' Remove duplicates in the data range
    wsData.Range("A1:E" & lastRow).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), Header:=xlYes   
    ' Calculate statistics for a given column (e.g., column B)
    ' Calculate the mean
    mean = Application.WorksheetFunction.Average(wsData.Range("B2:B" & lastRow))   
    ' Calculate the total
    total = Application.WorksheetFunction.Sum(wsData.Range("B2:B" & lastRow))   
    ' Calculate the standard deviation
    stdDev = Application.WorksheetFunction.StDev(wsData.Range("B2:B" & lastRow))   
    ' Display results in the Summary sheet
    wsSummary.Cells(1, 1).Value = "Statistics for Column B"
    wsSummary.Cells(2, 1).Value = "Mean"
    wsSummary.Cells(2, 2).Value = mean
    wsSummary.Cells(3, 1).Value = "Total"
    wsSummary.Cells(3, 2).Value = total
    wsSummary.Cells(4, 1).Value = "Standard Deviation"
    wsSummary.Cells(4, 2).Value = stdDev 
    ' End message
    MsgBox "Data analysis completed!"
End Sub

Explanation of the Code:

1. Variable Declarations:
   • wsData and wsSummary represent the worksheets containing the data and the summary.
   • lastRow stores the last used row in the data sheet (useful for defining the data range to analyze).
   • rng represents the range of data that we want to process.
   • mean, total, and stdDev store the results of the calculated statistics.
2. Accessing the Worksheets:
   • wsData refers to the sheet containing the data to analyze.
   • wsSummary is a new sheet created to display the statistics summary. If it already exists, the code retrieves it; otherwise, it creates a new sheet.
3. Cleaning the Data:
   • The first step is to remove empty rows. The IsEmpty function checks if a cell is empty, and cell.EntireRow.Delete removes the entire row if true.
   • Then, duplicates are removed from the data range (wsData.Range(« A1:E » & lastRow)) using the RemoveDuplicates method.
4. Calculating the Statistics:
   • The mean is calculated using Application.WorksheetFunction.Average.
   • The total is calculated using Application.WorksheetFunction.Sum.
   • The standard deviation is calculated using Application.WorksheetFunction.StDev.
5. Displaying the Results:
   • The results of the statistics are displayed in the Summary sheet starting from cell A1.
6. Completion Message:
   • A message box is displayed once the process is completed.

To Run the Code:

1. After pasting the code into the VBA editor, press Alt + F8, select AnalyzeData, and click Run.

Possible Extensions:

This code can be expanded to:

• Analyze multiple columns.
• Automatically generate charts (e.g., histograms, scatter plots).
• Automate more complex calculations (e.g., regression analysis, statistical tests).

This provides a solid foundation for automating data analysis in Excel using VBA.

1. Detailed VBA Code for Automating Data Exploration

Sub AutomateDataExploration()
    ' Variable declarations
    Dim wsSource As Worksheet
    Dim wsReport As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range
    Dim col As Long
    Dim average As Double
    Dim stdDev As Double
    Dim totalSum As Double
    Dim minValue As Double
    Dim maxValue As Double
    ' Create a new sheet for the report
    Set wsReport = ThisWorkbook.Sheets.Add
    wsReport.Name = "Analysis Report"
    ' Import data from a CSV file
    ' Ensure that the CSV file is in the same directory as your workbook
    Workbooks.Open Filename:="C:\Path\to\your\data.csv"
    Set wsSource = ActiveSheet
    ' Find the last used row in the source sheet
    lastRow = wsSource.Cells(wsSource.Rows.Count, 1).End(xlUp).Row
    ' Copy data from the source sheet to the report sheet
    wsSource.Range("A1").Resize(lastRow, wsSource.UsedRange.Columns.Count).Copy
    wsReport.Range("A1").PasteSpecial Paste:=xlPasteValues
    ' Data cleaning: Remove duplicates
    wsReport.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
    ' Identify statistics for each column
    For col = 1 To wsReport.UsedRange.Columns.Count
        ' Define the data range for the column
        Set dataRange = wsReport.Range(wsReport.Cells(2, col), wsReport.Cells(lastRow, col))
        ' Calculate average
        average = Application.WorksheetFunction.Average(dataRange)
        ' Calculate standard deviation
        stdDev = Application.WorksheetFunction.StDev(dataRange)
        ' Sum of values
        totalSum = Application.WorksheetFunction.Sum(dataRange)
        ' Minimum value
        minValue = Application.WorksheetFunction.Min(dataRange)
        ' Maximum value
        maxValue = Application.WorksheetFunction.Max(dataRange)
        ' Write results into the report
        wsReport.Cells(1, col + wsReport.UsedRange.Columns.Count).Value = "Statistics Column " & col
        wsReport.Cells(2, col + wsReport.UsedRange.Columns.Count).Value = "Average: " & average
        wsReport.Cells(3, col + wsReport.UsedRange.Columns.Count).Value = "Std Dev: " & stdDev
        wsReport.Cells(4, col + wsReport.UsedRange.Columns.Count).Value = "Sum: " & totalSum
        wsReport.Cells(5, col + wsReport.UsedRange.Columns.Count).Value = "Min: " & minValue
        wsReport.Cells(6, col + wsReport.UsedRange.Columns.Count).Value = "Max: " & maxValue
    Next col
    ' Format results for readability
    wsReport.Columns.AutoFit
    ' End message
    MsgBox "Data analysis is complete!", vbInformation
End Sub

2. Code Explanation

Variable Declarations

• wsSource and wsReport are used to reference the source worksheet (where the original data is) and the report worksheet (where the results will be displayed).
• lastRow helps to determine the number of rows in the source sheet that contain data.
• dataRange refers to the range of data in each column that will be analyzed.
• The other variables (average, stdDev, totalSum, minValue, maxValue) are used to store the calculated statistical results.

Data Import

• The code opens a CSV file located at a specified path (C:\Path\to\your\data.csv) and copies its data into the report sheet. You can replace the file path with the one pointing to your own data.

Data Cleaning

• The RemoveDuplicates method is used to remove any duplicate rows in the dataset. This ensures the data is clean before analysis.

Statistical Analysis

• For each column in the data, the code calculates several basic statistics:
  • Average: Mean value of the data in the column.
  • Standard Deviation (Std Dev): Measure of the spread of data.
  • Sum: Total of the values in the column.
  • Minimum Value: The smallest number in the column.
  • Maximum Value: The largest number in the column.
• These statistics are written into the report sheet next to the original data.

Formatting

• After the analysis, the code uses AutoFit to adjust the column widths for better readability.

End Message

• A message box pops up to inform the user that the data analysis has been completed.

3. Conclusion

This VBA code serves as a good starting point for automating the data exploration process in Excel. You can modify the code to include other types of analyses or integrate data from different sources. The data cleaning step (such as removing duplicates) can be expanded to handle missing values or other data quality issues. Additionally, the statistical functions can be adjusted or extended to calculate more advanced metrics depending on your needs

Objective:

• Merging Data: Combine data from multiple columns or sheets into one column or range.
• Splitting Data: Separate data in one column into multiple columns based on delimiters (like commas, spaces, etc.).

Detailed Example with Explanation

Here’s an example of VBA code that combines both merging and splitting data.

1. Merging Multiple Columns into One Column

Suppose you have data in columns A, B, and C in a worksheet, and you want to merge them into column D.

Sub MergeColumns()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    ' Define the active worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Find the last row with data in column A (or any other column)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Loop through each row and merge values from columns A, B, and C
    For i = 1 To lastRow
        ws.Cells(i, "D").Value = ws.Cells(i, "A").Value & " " & ws.Cells(i, "B").Value & " " & ws.Cells(i, "C").Value
    Next i
    MsgBox "Data merge complete!"
End Sub

Code Explanation:

• Variable Definitions: ws represents the active worksheet, and lastRow is used to find the last row with data in column A.
• For Loop: It loops through each row and merges the values from columns A, B, and C with a space between them. The merged value is placed in column D.
• MsgBox: Displays a message when the operation is complete.

2. Splitting a Column of Data into Multiple Columns

If you have a column (e.g., column D) containing data separated by commas, and you want to split this data into separate columns, you can use the following code:

Sub SplitColumns()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim SplitData() As String
    ' Define the active worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Find the last row with data in column D
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
    ' Loop through each row in column D
    For i = 1 To lastRow
        ' Split the data in cell D(i) using a comma as the delimiter
        SplitData = Split(ws.Cells(i, "D").Value, ",")     
        ' Assign each element of the array to a new column (E, F, G, etc.)
        Dim j As Long
        For j = LBound(SplitData) To UBound(SplitData)
            ws.Cells(i, j + 5).Value = SplitData(j) ' Start placing data from column E (column 5)
        Next j
    Next i
    MsgBox "Data split complete!"
End Sub

Code Explanation:

• Split(): This function splits the data in cell D(i) into an array SplitData(), using a comma (,) as the delimiter.
• For Loop: The outer loop iterates through the rows, while the inner loop goes through each element of the SplitData() array, placing each value into subsequent columns (starting from column E).
• LBound() and UBound(): These functions return the lower and upper bounds of the array, allowing you to loop through all the elements.
• Completion Message: A message box appears when the splitting operation is done.

How to Use These Macros:

1. Access the VBA Editor:
   • Press Alt + F11 to open the VBA editor.
   • Click Insert > Module to insert a new module where you can paste the code.
2. Copy and paste the code into the module.
3. Run the Macro:
   • Go to the « Developer » tab in Excel, then click on « Macros » and select the macro you want to run (e.g., MergeColumns or SplitColumns).

Possible Enhancements:

1. Add Conditions to only merge specific rows or values (e.g., skip empty rows).
2. Handle Other Delimiters like spaces, tabs, or semicolons in the splitting function.
3. Add Error Handling to ensure the data is merged or split without losing any information.

Using these two macros, you can automate the process of merging and splitting data in your Excel files. Feel free to customize the scripts to fit your specific needs.

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.