Copy data (including formulas) from one Excel file to another using VBA

Let us assume, I have an Excel file in a folder and I want to copy data as it is into another Excel file (a master file) using VBA. When I am saying, as it is, I mean the macro should copy data from sheet to sheet (create or rename sheets in destination file), copy formulas (if any) etc.

The Source file

The source Excel file may have n number of sheets.

The file (for this example), has 3 worksheets: April, May and Sheet3. Worksheet April has sales numbers along with cells with formula (4th column). Remaining two sheets have similar data. See the below image.

The source file

Cells with formula in Excel

Now, let's copy data from the source to the destination.

The Macro

Write the below macro in the "destination" (or the master) file's Workbook_Open() event. You can also write the code in a Module.

Option Explicit

Private Sub Workbook_Open()
    Call copyDataFromAnotherFile
End Sub

Sub copyDataFromAnotherFile()
    On Error GoTo ErrHandler

    Application.ScreenUpdating = False

    Dim src As Workbook
    Set src = Workbooks.Open("D:\sample.xlsx", True, True)      ' Open the source file in "read only" mode.
    
    Dim iSheetCount As Integer
    iSheetCount = 1
    
    Dim srcWs As Worksheet
    For Each srcWs In src.Sheets
        ' Get source row and column count.
        Dim iRows, iCols As Integer
        iRows = src.Worksheets(srcWs.Name).UsedRange.Rows.Count
        iCols = src.Worksheets(srcWs.Name).UsedRange.Columns.Count
        
        Worksheets(iSheetCount).Name = srcWs.Name         ' Rename the worksheet.

        Dim iC1, iC2 As Integer       ' just counters.

        ' Start copying data from source to destination.
        For iC1 = 1 To iRows
            For iC2 = 1 To iCols
                ' Check if cell (in source) has formula.
                If src.Worksheets(srcWs.Name).Cells(iC1, iC2).HasFormula() Then
                    Worksheets(srcWs.Name).Cells(iC1, iC2) = src.Worksheets(srcWs.Name).Cells(iC1, iC2).Formula
                Else
                    Worksheets(srcWs.Name).Cells(iC1, iC2) = src.Worksheets(srcWs.Name).Cells(iC1, iC2)
                End If
            Next iC2
        Next iC1
        
        ' Add a new sheet (to this workbook or destination file) if sheet does not exist.
        If (Worksheets.Count < src.Worksheets.Count) Then
            Sheets.Add After:=src.Worksheets(srcWs.Name)
            iSheetCount = iSheetCount + 1
        End If
    Next
    
    ' Done! Now close the source file.
    src.Close False       ' False - Do not save the source file.
    Set src = Nothing

ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

The above code executes when you open the "master" (or the destination) Excel file. Or, you can click F5 button to see it work.

It first opens the source workbook, which is located in "D:\" folder.

Dim src As Workbook
Set src = Workbooks.Open("D:\sample.xlsx", True, True)

It loops through each "worksheet" in the source file and looks for data.

The destination Excel file may or may not have the worksheets that are in source file. Any ways, it will rename the sheet.

Worksheets(iSheetCount).Name = srcWs.Name

Next, the macro copies data from source to destination including the formulas.

It checks if the destination (or master) file has the same number of worksheets. If not, it creates (or adds) a new worksheet in the destination Excel file.

If (Worksheets.Count < src.Worksheets.Count) Then
    Sheets.Add After:=src.Worksheets(srcWs.Name)
    iSheetCount = iSheetCount + 1
End If

All done! It closes the source file.

src.Close False
Set src = Nothing

image

copy data from one excel file to another using vba

← PreviousNext →