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.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Place the following code on a Standard Module in Target workbook and see if you get the desired output.

The code assumes that the workbook Source.xlsx is placed on your desktop and target workbook can be placed at a different location also. The name of your source workbook is assumed to be as Source with .xlsx file extension.

Sub CopyDataFromSourceToTarget()
Dim swb As Workbook, twb As Workbook
Dim sws As Worksheet
Dim fPath As String

fPath = Environ("UserProfile") & "\Desktop\Source.xlsx"

Application.ScreenUpdating = False
Set twb = ThisWorkbook

Set swb = Workbooks.Open(fPath)
For Each sws In swb.Sheets
    On Error Resume Next
    sws.Range("D20:F30").Copy twb.Sheets(sws.Name).Range("D20")
Next sws
swb.Close False
Application.ScreenUpdating = True
MsgBox "Data has been copied from the Source Workbook into Target Workbook successfully.", vbInformation, "Done!"
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbfromnewjerseyAuthor Commented:
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 TeotiaCommented:
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..

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.