Copy/paste cell data from selected cells in row

2
votes

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.

2
excelvba
Are you just copying a single row per run of the macro? – Tim Williams
Yes - but my problem is that the cell is always going to be different because every run of the macro will be on a different row. Same with the destination file, every row will be on a different line so the cell will never be the same. That's what has me stuck, because the macro records the specific cells that I choose when recording so it always copies/pastes the same thing over to the same location. – Eric Morrison

2 Answers

1
votes

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
0
votes

You need to come up with an internal logic of what cells get copied and where are they pasted to, after you do so, you can adapt the following code to suit your needs:

Sub Test2()

    'Define variables

    Dim wkCopy As Workbook
    Dim wkDest As Workbook

    Dim wsCopy As Worksheet
    Dim wsDest As Worksheet

    ' Change names as required
    Set wsCopy = Workbooks("Joey's May FY19 Audit.xlsm").Worksheets("Sheet1")
    Set wsDest = Workbooks("Master Files - May.xlsm").Worksheets("Sheet1")

    ' Copy a range from the source sheet and paste in the destination one
    wsCopy.Range("A1:A10").Copy wsDest.Range("B11")

End Sub