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
Now, let's copy data from the source to the destination.
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
Simple and yet useful.
Happy coding. ☺