Copy data from one excel file to another, matching sheet names

[DachoR]

Hi guys,
I have two excel files with over 100 sheets each. Now I have to copy all data from one file to another, to match the sheet names, for example from excel file A copy data from Sheet1 to excel file B to Sheet1, and so on for all matching sheets.
Is this possible?
Thanks.

 

[daregu]

Power Query can do this for you.

Combine Data from Multiple Workbooks in Excel (using Power Query)

Power Query can be of great help when you want to combine data from multiple workbooks into one single worksheet. Here is a step-by-step guide on how to do this in Excel. You can combine data when it's stored in an Excel table, or even when it's not formatted as a table.

trumpexcel.com

 

[DachoR]

Thanks, but I would like to have an excel macro for this purpose.

 

[Joe4]

If you want to make an exact copy of it, wouldn't it just be easier to re-save the original file under the new name?

 

[DachoR]

One file is a template, and every month I have to add new data in it.
I can make some workaround by recording a Macro with just copy-paste of data, but I was just wondering if here could be a smarter solution.

 

[Joe4]

If you have over 100 sheets that are all formatted similarly, it sounds to me like what you really have is a Relational Database, and using a Relational Database program like Microsoft Access or SQL would be far more efficient and easier to work with (though there is some leg work in setting it up).

Though you can use Excel as a Relational Database (and people do), it is typically clunky and inefficient because that is really not what Excel was designed for.
And you may come to a point when there is just too much data for Excel to handle.

If you absolute must keep it in Excel, there may be some things you can do to handle this task a little more gracefully, but I think we would need more information on exactly what the steps are that you are doing, and how the workbooks and worksheets are designed.

 

[DachoR]

I need to have 100 different sheets in excel and data has to be separated.
Lets say we have two excel files A and B, each of them has 100 sheets, and file B is a template with formulas with also 100 sheets.
So I need to copy all data from file A from all sheets to file B, but to corresponding Sheet name.
Steps:
Copy all data from file A, sheet1, to file B sheet1, then go back to file A and copy data from sheet2 to file B also sheet2, and so on, until all data from all file A sheets are copied to corresponding sheets of file B.
Hope this makes sense.

 

[Joe4]

What are the exact ranges you want copied?
If you copy over the entire sheet, you will overwrite all the formulas and any formatting you have in your template.
Are you just copying over certain columns?
Are you copying over the header row, if you have one?

If you data is all contiguous (no completely blank rows or columns in the middle of your data), we may be able to use Current Region to dynamically select all the data.

 

[DachoR]

Range can be A1:L200
Formulas are in another sheets, they just collect data from sheets that need to be updated
No headers, no certain columns

The most important thing is to match sheet names from two different files and copy data from one to another

 

[Joe4]

See if this does what you want:

Sub Get_Data_From_File()

    Dim file1 As Variant
    Dim wb1 As Workbook
    Dim file2 As Variant
    Dim wb2 As Workbook
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
'   Browse for data file and open it
    file1 = Application.GetOpenFilename(Title:="Browse for your Data File", FileFilter:="Excel Files (*.xls*),*xls*")
    If file1 <> False Then
        Set wb1 = Application.Workbooks.Open(file1)
    End If

'   Browse for template file and open it
    file2 = Application.GetOpenFilename(Title:="Browse for your Template File", FileFilter:="Excel Files (*.xls*),*xls*")
    If file2 <> False Then
        Set wb2 = Application.Workbooks.Open(file2)
    End If

'   Loop through all sheets in data file and copy over to template file
    wb1.Activate
    For Each ws In wb1.Worksheets
        ws.Cells.Copy
        wb2.Activate
        Sheets(ws.Name).Activate
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    Next ws
    
'   Close data files
    wb1.Close
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub