Copy Data From one Workbook to another

[Wolfgang17]

I would like to copy data from a single range of cells from multiple sheets to the same range and sheets in another workbook. The workbooks are identical other than the missing cell values.
Any help would be appreciated.
Some of the code has been commented out as trial end error.

Sub CopyData()

Dim x As Workbook
Dim y As Workbook
Dim CopyRng As Range

'## Open both workbooks first:
Set x = Workbooks.Open(" C:\Desktop\Copy Test/2017 Timecard_Original.xlsm ")
Set y = Workbooks.Open(" C:\Desktop\Copy Test/2017 Timecard_Backup.xlsm ")



'Now, transfer values from x to y:
y.Sheets("Pay1").Range("B5:N47").Value = x.Sheets("Pay1").Range("B5:N47")


' Loop through all worksheets and copy the data to the
               
             y.Activate
             
    For Each sh In ActiveWorkbook.Sheets(Array("Pay1", "Pay2", "Pay3", "Pay4", "Pay5", "Pay6", "Pay7", "Pay8", "Pay9", _
        "Pay10", "Pay11", "Pay12", "Pay13", "Pay14", "Pay15", "Pay16", "Pay17", "Pay18", "Pay19" _
        , "Pay20", "Pay21", "Pay22", "Pay23", "Pay24", "Pay25", "Pay26"))
        
         ' Specify the range to place the data.
            'Set CopyRng = sh.Range("B5:N47")
            'With DestSh.Cells("B5")
            
'This statement copies values from each worksheet.
            'CopyRng.Copy
            '.PasteSpecial xlPasteValues
            
            Application.CutCopyMode = False
            End With

 

[JLGWhiz]

I'm not sure about the path because yours shows spaces after and before quote marks and a forward slash before the file name, but the rest of the code should work.

Sub CopyData2()
Dim wb1 As Workbook, wb2 As Workbook, shAry As Variant
Set wb1 = Workbooks.Open("C:\Desktop\Copy Test\2017 Timecard_Original.xlsm") 'validate path
Set wb2 = Workbooks.Open("C:\Desktop\Copy Test\2017 Timecard_Backup.xlsm") 'Validate path
shAry = Array("Pay1", "Pay2", "Pay3", "Pay4", "Pay5", "Pay6", "Pay7", "Pay8", "Pay9", _
        "Pay10", "Pay11", "Pay12", "Pay13", "Pay14", "Pay15", "Pay16", "Pay17", "Pay18", "Pay19" _
        , "Pay20", "Pay21", "Pay22", "Pay23", "Pay24", "Pay25", "Pay26")
    For i = LBound(shAry) To UBound(shAry)
        wb1.Sheets(shAry(i)).Range("B5:N47").Copy wb2.Sheets(shAry(i)).Range("B5")
    Next
End Sub

 

[Wolfgang17]

Yes that worked.
Thank You Very Much!

 

[Joe4]

If you sheet names are really that straightforward and sequential, you could shorten up your code like this:

Sub CopyData2()

    Dim wb1 As Workbook, wb2 As Workbook, i As Long

    Set wb1 = Workbooks.Open("C:\Desktop\Copy Test\2017 Timecard_Original.xlsm") 'validate path
    Set wb2 = Workbooks.Open("C:\Desktop\Copy Test\2017 Timecard_Backup.xlsm") 'Validate path
    
    For i = 1 To 26
        wb1.Sheets("Pay" & i).Range("B5:N47").Copy wb2.Sheets("Pay" & i).Range("B5")
    Next
    
End Sub

 

[Wolfgang17]

Yes there are , this worked nicely.
Thanks Again

 

[JLGWhiz]

Yes that worked.
Thank You Very Much!

You're welcome,
regards, JLG