Copy Cell Value from Multiple sheets to single Column in another Workbook

[Wolfgang17]

HI,
I am trying to copy Range W3 from sheets "Pay10" to "Pay26" in one workbook to a sheet called "Data" Range C17:C33 in another workbook.
Any help would be appreciated.

Sub VacationPayTransfer()

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

    Set wb1 = Workbooks.Open("C:\Desktop\\2017 Timecard.xlsm") 'validate path
    Set wb2 = Workbooks.Open("C:\Desktop\\2018 Timecard.xlsm") 'Validate path
    
    For i = 10 To 26
        wb1.Sheets("Pay" & i).Range("W3").Copy
        wb2.Activate
        wb2.Sheets("Data").Range("C17:C33").PasteSpecial xlPasteValues
    Next
    
End Sub

 

[mumps]

Try:

Sub VacationPayTransfer()
    Application.ScreenUpdating = False
    Dim wb1 As Workbook, wb2 As Workbook, i As Long
    Dim DesktopAddress As String
    DesktopAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    ChDir DesktopAddress
    Set wb1 = Workbooks.Open(DesktopAddress & "\" & "2017 Timecard.xlsm")
    Set wb2 = Workbooks.Open(DesktopAddress & "\" & "2018 Timecard.xlsm")
    For i = 10 To 26
        wb1.Sheets("Pay" & i).Range("W3").Copy
        wb2.Sheets("Data").Range("C17:C33").PasteSpecial xlPasteValues
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[Wolfgang17]

For some reason the code copies the value of the cell V3 not W3. I've had this same thing happen in other coding attempts. Not sure why.

 

[mumps]

The code definitely says

Range("W3").Copy

so I don't know why it would be copying V3 instead. Since you've had this problem before, it would be easier to check things out if I could see your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.