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.

 

[narinder_73]

Also note that the sheet names in BOTH files must match EXACTLY for anything to be copied over.
So you would need sheets named ABC and DEF in BOTH files.

Yes it is same

 

[Joe4]

At this point, it is hard to tell what might be going on without having access to the two files you are working on.
Would you be able to upload the two files to a file sharing site and provide a link to them here?
Just be sure to remove any sensitive data before you do.

 

[narinder_73]

At this point, it is hard to tell what might be going on without having access to the two files you are working on.
Would you be able to upload the two files to a file sharing site and provide a link to them here?
Just be sure to remove any sensitive data before you do.

Data.xlsx
	A	B	C	D	E
1	aa	bb	cc	dd	ee
2	11	11	11	11	11
3	22	22	22	22	22
4	33	33	33	33	33
5	44	44	44	44	44
6	55	55	55	55	55
7	66	66	66	66	66
8	77	77	77	77	77
9	88	88	88	88	88
10	99	99	99	99	99
11	110	110	110	110	110
ABC

Data.xlsx
	A
1	b
2	DEF
3	DEF
4	DEF
5	DEF
6	DEF
7	DEF
8	DEF
9	DEF
10	DEF
DEF

Template.xlsx
	A	B	C	D	E
1	aa	bb	cc	dd	ee
ABC

Template.xlsx
	A
1	a
DEF

 

[Joe4]

Very odd. It didn't work when I ran it, but it did when I manually stepped through it.
I added some more explicit workbook/sheet references in the copy/paste loop, and that seems to have fixed it.

Try this:

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
        wb2.Sheets(ws.Name).Activate
        wb2.Sheets(ws.Name).Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    Next ws
    
'   Close data files
    wb1.Close
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

 

[narinder_73]

Very odd. It didn't work when I ran it, but it did when I manually stepped through it.
I added some more explicit workbook/sheet references in the copy/paste loop, and that seems to have fixed it.

Try this:

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
        wb2.Sheets(ws.Name).Activate
        wb2.Sheets(ws.Name).Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    Next ws
   
'   Close data files
    wb1.Close
   
    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!"
   
End Sub

Thanks
It also worked manually but not working when ran

 

[Joe4]

Thanks
It also worked manually but not working when ran

It works for me on the data samples you provided.
At the end of the Macro, the populated Template file is left open.

You can try removing this line of code:

    MsgBox "Macro complete!"

I don't think that should matter, but go ahead and try.

Unfortunately, I think that is all I can really do here.
If I cannot recreate your issue, I cannot fix it.

If it were me, and I were doing this from scratch, I think I would go about this a little differently.
I would put the VBA code in the Template file, and then re-write the code to browse for the data file, and to import all the data from that into the Template.
Since we would only be working with 2 files and not 3 in that instance, I think things may work a little better.

 

[Joe4]

Here is VBA code that I put right in the Template file.
I tested it out on your data samples, and it worked for me.

Sub Get_Data_From_File2()

    Dim wb1 As Workbook
    Dim file2 As Variant
    Dim wb2 As Workbook
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
'   Set current workbook
    Set wb1 = ActiveWorkbook
    
'   Browse for data file and open it
    file2 = Application.GetOpenFilename(Title:="Browse for your Data 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.Activate
        wb2.Activate
        wb2.Sheets(ws.Name).Activate
        wb2.Sheets(ws.Name).Cells.Copy
        wb1.Activate
        ws.Activate
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    Next ws
    
'   Close data files
    wb2.Close
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete!"
    
End Sub

 

[narinder_73]

It works for me on the data samples you provided.
At the end of the Macro, the populated Template file is left open.

You can try removing this line of code:

    MsgBox "Macro complete!"

I don't think that should matter, but go ahead and try.

Unfortunately, I think that is all I can really do here.
If I cannot recreate your issue, I cannot fix it.

If it were me, and I were doing this from scratch, I think I would go about this a little differently.
I would put the VBA code in the Template file, and then re-write the code to browse for the data file, and to import all the data from that into the Template.
Since we would only be working with 2 files and not 3 in that instance, I think things may work a little better.

Yes your suggestion is very valid.
if you can give new code in two sheets I will be happy but also take care that the data sheets may be less than template sheets.
As my self is civil engineer having little knowledge of Macros. Your help needed very much

 

[narinder_73]

Yes your suggestion is very valid.
if you can give new code in two sheets I will be happy but also take care that the data sheets may be less than template sheets.
As my self is civil engineer having little knowledge of Macros. Your help needed very much

Dear Joe
There are now two points
1. It should be reversed i.e. putting template on top of data of file1
2. Top row should be deleted
3. as you mentioned should use only two sheets

 

[Joe4]

Yes your suggestion is very valid.
if you can give new code in two sheets I will be happy but also take care that the data sheets may be less than template sheets.
As my self is civil engineer having little knowledge of Macros. Your help needed very much

Do you try the new code I posted after that reply yesterday?