excel - Copy/paste cell data from selected cells in row

My job requires that I copy/paste data from multiple cells in a row from one Excel worksheet to specific cells in another Excel worksheet.

I created a macro, but it is only selecting the specific cells I selected when recording the macro - these cells will always change depending on which row I'm needing the data from, so the recorded macro is useless. Also, the location of where this info will be pasted on the other worksheet will always be different (the macro copies the same exact data selected when recording and pastes it in the same exact location selected to paste when recording). I'm a noob at VBA, so I'm unsure on how to accomplish this.

I basically want to specify a specific row to start copying data from, and always select the same cells on that specific row as well as paste the data to specific cells on whatever row I have selected on the destination worksheet. Can someone help? This is for my job - any help would be appreciated. Ideally I'd like the macro to tab over to the specific cells needed and copy that info, from whatever row the macros starts on - and then paste that data to the cells selected via tabbing over on whatever row is selected in the other worksheet at the time of the macro being ran, if that makes sense.

Answer

You can do something like this (untested):

Sub CopyOver()


    Dim rwSrc As Long, rwDest As Long
    Dim shtSrc As Worksheet, shtDest As Worksheet
    Dim p, arr

    'assume macro is run with the source workbook active
    Set shtSrc = ActiveSheet
    rwSrc = Selection(1).Row  '(1) = first selected cell only

    'switch to the "other" workbook and get the selection
    Workbooks("Joey's May FY19 Audit.xlsx").Activate

    Set shtDest = ActiveSheet
    rwDest = Selection(1).Row

    'Example [source:destination] pairs ColA>>ColB, B>>C, F>>H, etc
    For Each p In Array("A:B", "B:C", "F:H")
        arr = Split(p, ":")
        shtSrc.Cells(rwSrc, arr(0)).Copy shtDest.Cells(rwDest, arr(1))
    Next p

End Sub