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

DachoR

New Member
Joined
Jan 12, 2017
Messages
33
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

daregu

New Member
Joined
Jun 16, 2021
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Power Query can do this for you.
 

DachoR

New Member
Joined
Jan 12, 2017
Messages
33
Thanks, but I would like to have an excel macro for this purpose.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,031
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 12, 2017
Messages
33

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,031
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 12, 2017
Messages
33

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,031
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 12, 2017
Messages
33
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,031
Office Version
  1. 365
Platform
  1. Windows
See if this does what you want:
VBA Code:
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
 
Solution

Forum statistics

Threads
1,147,517
Messages
5,741,631
Members
423,674
Latest member
Charles2dodo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top