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.

 

[DachoR]

This works exactly what I wanted!
Cheers mate, you are a genius!

 

[Joe4]

This works exactly what I wanted!
Cheers mate, you are a genius!

Excellent! Glad to hear works for you!

 

[narinder_73]

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

While using the same in Excel 2019 getting Runtime error 1004 in loop
line Range("A1").Select
need help in this

 

[Joe4]

While using the same in Excel 2019 getting Runtime error 1004 in loop
line Range("A1").Select
need help in this

Is there anything special about cell A1?
Is it hidden, protected, or merged?

 

[narinder_73]

Is there anything special about cell A1?
Is it hidden, protected, or merged?

No there is nothing special in A1
Neither hidden nor protected or merged

 

[jxb]

Do you actually need to activate wb2 and do the the .select?
wb2.worksheet(was.name).Range(“A1”).paste

 

[narinder_73]

Do you actually need to activate wb2 and do the the .select?
wb2.worksheet(was.name).Range(“A1”).paste

Please guide where to enter, I don't know VBA
Thanks for taking pain for me. see in image this is coping but not pasting on wb2
This is sample data on which I am trying but actual data is large
Wb1

Wb2

 

[narinder_73]

The

Do you actually need to activate wb2 and do the the .select?
wb2.worksheet(was.name).Range(“A1”).paste

Where to add this

 

[jxb]

something like that

    For Each ws In wb1.Worksheets
        ws.Cells.Copy
        wb2.Sheets(ws.Name).Range("A1").Paste
    Next ws

 

[narinder_73]

something like that

    For Each ws In wb1.Worksheets
        ws.Cells.Copy
        wb2.Sheets(ws.Name).Range("A1").Paste
    Next ws

the given is not working
I done but still showing error
its working up to copy and not doing paste.

wb2.Activate
For Each ws In wb1.Worksheets
ws.Cells.Copy
wb1.Activate
Sheets(ws.Name).Activate
Range("A1").Select
wb2.Worksheet(ws.Name).Range(“A1”).Paste
'ActiveSheet.Paste
Application.CutCopyMode = False
Next ws