copy data in Excel

I have two Excel files. Each one has multiple tabs/worksheets.  They are set up exactly the same except that File A contains (production) data and File B doesn't.  Both files contain column headings, totals lines, etc.

I need a routine that will copy the values from a specified range in each tab of File A and paste them in each corresponding tab of File B.

So, for example, let's say each file contains tabs/worksheets named/numbered "1" through "10" and that the range I need to copy is C10 to G20.
What I need in plain English is:

copy values from File A, worksheet "1" cells C10 to G20 and paste them into File B, worksheet "1" cells C10 to G20
copy values from File A, worksheet "2" cells C10 to G20 and paste them into File B, worksheet "2" cells C10 to G20
copy values from File A, worksheet "3" cells C10 to G20 and paste them into File B, worksheet "3" cells C10 to G20
etc. until all 10 worksheets in File B are populated

Within a given file, the range of cells that needs to be copied will be consistent from one worksheet to the next.
However, it would be helpful if the range could be specified through an input box so that the routine could be used with other similar files where the range of cells needed to be copied is different.  Thank you.
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.

Patrick MatthewsCommented:
This should do it. You get prompted to select source workbook, destination workbook, and the range to copy from.

Sub CopyTheData()
    Dim SourcePath As String
    Dim DestPath As String
    Dim SourceName As String
    Dim DestName As String
    Dim SourceWasOpen As Boolean
    Dim DestWasOpen As Boolean
    Dim SourceWb As Workbook
    Dim DestWb As Workbook
    Dim NumOfWs As Long
    Dim Counter As Long
    Dim SourceWs As Worksheet
    Dim DestWs As Worksheet
    Dim AddressToCopy As String
    SourcePath = Application.GetOpenFilename("Excel files, *.xls*", , "Select source file", , False)
    SourceName = Mid(SourcePath, InStr(1, SourcePath, "\") + 1)
    DestPath = Application.GetOpenFilename("Excel files, *.xls*", , "Select destination file", , False)
    DestName = Mid(DestPath, InStr(1, DestPath, "\") + 1)
    On Error Resume Next
    Set SourceWb = Workbooks(SourceName)
    If Err = 0 Then
        SourceWasOpen = True
        SourceWasOpen = False
        Set SourceWb = Workbooks.Open(SourcePath)
    End If
    Set DestWb = Workbooks(DestName)
    If Err = 0 Then
        DestWasOpen = True
        DestWasOpen = False
        Set DestWb = Workbooks.Open(DestPath)
    End If
    On Error Resume Next
    NumOfWs = SourceWb.Worksheets.Count
    For Counter = 1 To NumOfWs
        If Counter = 1 Then
            AddressToCopy = Application.InputBox("Select the range to copy", "Data Copier", , , , , , 8).Address
        End If
        Set SourceWs = SourceWb.Worksheets(1)
        Set DestWs = DestWb.Worksheets(1)
        SourceWs.Range(AddressToCopy).Copy DestWs.Range(AddressToCopy)
    If Not SourceWasOpen Then SourceWb.Close False
    If Not DestWasOpen Then DestWb.Close False
    MsgBox "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
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.