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]

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

1. So you want to copy the data from the template file to the data file? Do you want to put the VBA code in the template file or the data file?
2. Do you mean the sheet you are copying from? To exclude the first row?

 

[narinder_73]

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

Yes
That's working
thanks a lot for your help

 

[narinder_73]

1. So you want to copy the data from the template file to the data file? Do you want to put the VBA code in the template file or the data file?
2. Do you mean the sheet you are copying from? To exclude the first row?

1. Yes i want to put template file2 to file1 in first row. want to put VBA in template
2. Sheet to copied from file2 and to be inserted at file1 in row 1

 

[Joe4]

Yes
That's working
thanks a lot for your help

So does this mean you are all set, or you still need something?

 

[narinder_73]

So does this mean you are all set, or you still need something?

That set all
now need reverse as mentioned

 

[Joe4]

That set all
now need reverse as mentioned

I am confused.
If you already have a method that works, why do you need another one?

 

[narinder_73]

I am confused.
If you already have a method that works, why do you need another one?

This is done
Sometimes need to insert template to data. So i need the reverse of this also.

 

[Joe4]

This is done
Sometimes need to insert template to data. So i need the reverse of this also.

It should then just be a matter of putting the VBA code in the other file, and just changing the selection of the file to the other file.
The changes to the code should be minimal.

Why don't you go ahead and see if you can make the minor edits yourself, and let us know if you get stuck?
This will be a good exercise for you to help you understand the code and how to write/edit it.
(After all, my main goal is to teach you, not to do it all for you! ).

 

[narinder_73]

It should then just be a matter of putting the VBA code in the other file, and just changing the selection of the file to the other file.
The changes to the code should be minimal.

Why don't you go ahead and see if you can make the minor edits yourself, and let us know if you get stuck?
This will be a good exercise for you to help you understand the code and how to write/edit it.
(After all, my main goal is to teach you, not to do it all for you! ).

Yes
But I stuck at one point which is to first insert one row at top then paste in top row

 

[Joe4]

But I stuck at one point which is to first insert one row at top then paste in top row

Here is a little trick you can use to quickly get snippets of code.
If you turn on your Macro Recorder, and record yourself performing an action manually (like inserting a blank row in row 1), then stop the Macro Recorder, that will give you VBA code that will do that for you. So you can view your recorded code, and copy/paste the section that performs that copy to your other code.

This is a great little tool that even us "pros" use from time-to-time (we don't memorize all this stuff).