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.

 

[Joe4]

What is the name of the module where you have placed your VBA code?

 

[narinder_73]

What is the name of the module where you have placed your VBA code?

I kept in another workbook named Book1 pasted in developer

 

[Joe4]

You have it in the the Sheet module. I don't think it will work well here.
You should place it in a new General module (the Sheet modules are mostly for Event Procedure VBA code).
See here: Insert and run VBA macros in Excel - step-by-step guide

If you do that and move the code to a General Module, does it run any better?

 

[narinder_73]

You have it in the the Sheet module. I don't think it will work well here.
You should place it in a new General module (the Sheet modules are mostly for Event Procedure VBA code).
See here: Insert and run VBA macros in Excel - step-by-step guide

If you do that and move the code to a General Module, does it run any better?

Thanks
Now Macro is not giving any error but not getting result. Macro passes without pasting the data in template

 

[Joe4]

Thanks
Now Macro is not giving any error but not getting result. Macro passes without pasting the data in template

Sometimes, things don't happen the way you think they are (i.e. you may not be in the correct file or on the correct sheet when you hit a certain part of the code).
I find it is most helpful to step through the code line-by-line using the F8 and watch what is happening each step of the way (easy to do if you have two monitors - have one with worksheet and one with code; otherwise, split the screen so you can do that).
Quite often, when you see what is happening each step of the way, the issue suddenly becomes quite obvious.

 

[narinder_73]

Sometimes, things don't happen the way you think they are (i.e. you may not be in the correct file or on the correct sheet when you hit a certain part of the code).
I find it is most helpful to step through the code line-by-line using the F8 and watch what is happening each step of the way (easy to do if you have two monitors - have one with worksheet and one with code; otherwise, split the screen so you can do that).
Quite often, when you see what is happening each step of the way, the issue suddenly becomes quite obvious.

Thanks, as using your lot of time and energy.
Already done by using F8, it pass through and don't give any error. But with later code have an error.

 

[Joe4]

Already done by using F8, it pass through and don't give any error. But with later code have an error.

OK, let's make sure that we are on the same page here with all the details:
1. Which file is this VBA code in?
2. What is the name of the module the VBA code is in?
3. Which line iis currently erroring, and what is the exact error message?
4. If you have made any changes to the VBA code since last posting it, re-post the version you are using now.

 

[narinder_73]

Sometimes, things don't happen the way you think they are (i.e. you may not be in the correct file or on the correct sheet when you hit a certain part of the code).
I find it is most helpful to step through the code line-by-line using the F8 and watch what is happening each step of the way (easy to do if you have two monitors - have one with worksheet and one with code; otherwise, split the screen so you can do that).
Quite often, when you see what is happening each step of the way, the issue suddenly becomes quite obvious.

I checked again found following As I have two sheets names ABC and DEF, it selects first DEF in Data and copy file then does not activate file 2 and paste

 

[Joe4]

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.

 

[narinder_73]

OK, let's make sure that we are on the same page here with all the details:
1. Which file is this VBA code in?
2. What is the name of the module the VBA code is in?
3. Which line iis currently erroring, and what is the exact error message?
4. If you have made any changes to the VBA code since last posting it, re-post the version you are using now.

1. VBA code is not in any file (i.e. file 1 and file 2) it is in another file which I named Book1.
2. it is named module 1
3. While running your original code no line currently erroring. if i use your later code it gives error on line 'wb2.Worksheet(ws.Name).Range(“A2”).Paste'
4. I am posting the code here
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
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(“A2”).Paste
ActiveSheet.Paste
Application.CutCopyMode = False
Next ws


' Close data files
wb1.Close

Application.ScreenUpdating = True

MsgBox "Macro complete!"

End Sub