Copy a range of cells between two Excel files

I have two Excel files. They both reside on the Desktop of my C: drive. One is named Source. One is named Target. Each one has 10 tabs named "1", "2", "3", "4" etc. I need a routine that will copy a range of cells (D20 to F30), including the source formatting, from each tab of the source file to each related tab of the target file (i.e. from tab 1 of the source file to tab 1 of the target file, tab 2 of the source to tab 2 of the target, etc.) The data is different in each tab of the source file but the range of cells in each tab that I need to copy over is the same.

Thanks.

Microsoft Excel

Last Comment

Saurabh Singh Teotia

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

9/2/2015

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Jenkins

9/2/2015

ASKER

The macros through the end as evidenced by the message box message but nothing gets populated in the target file. By the way, I'm dealing with macro-enabled worksheets, so I had to change the extension to .xlsm.

Again, what I'm trying to accomplish is have the data in the selected range from tab1 be copied into that range in tab 1 of the target file, data in the selected range from tab 2 be copied into that range in tab 2 of the target file, etc, etc..... for all tabs in the workbook. The number of tabs and names of tabs are the same in both files.

Saurabh Singh Teotia

9/2/2015

In sktneer code...

Change line no-->14 which is this...

sws.Range("D20:F30").Copy twb.Sheets(sws.Name).Range("D20")

Open in new window

To this...

sws.Range("D20:F30").Copy twb.Sheets("" & sws.Name & "").Range("D20")

Open in new window

This should fix what you are trying to do..

Saurabh...

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy