[RESOLVED] [Excel] Copying and pasting cells from a selected row

[Toothy]

Hi there,

I am new to coding of all kinds and have run into problems when trying to write a script to do the following in Excel 2010. I have been googling for the last couple of days and have not been able to find an answer, so would appreciate your help!

I want to select a row in a master spreadsheet ("Master worksheet in progress v0.2"), then run a script which will then open another spreadsheet ("Handover Action Form") and copy a number of non-consecutive cells in the selected row and paste them into specific cells in the second spreadsheet.

So far, I have recorded a macro that opens the second worksheet, then copy and pastes from fixed cells:

Sub CopyPastetoHandover()
'
' CopyPastetoHandover Macro
'

'
    Workbooks.Open Filename:="H:\My Documents\HL tools\Handover Action Form.xlsm" _
        , UpdateLinks:=0
    Windows("Master worksheet in progress v0.2.xlsm").Activate
    Range("A2").Select
    Selection.Copy
    Windows("Handover Action Form.xlsm").Activate
    Range("A8").Select
    ActiveSheet.Paste
    Windows("Master worksheet in progress v0.2.xlsm").Activate
    Range("H2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Handover Action Form.xlsm").Activate
    Range("F4").Select
    ActiveSheet.Paste
    Windows("Master worksheet in progress v0.2.xlsm").Activate
    Range("J2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Handover Action Form.xlsm").Activate
    Range("B8").Select
    ActiveSheet.Paste
    Windows("Master worksheet in progress v0.2.xlsm").Activate
    Range("K2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Handover Action Form.xlsm").Activate
    Range("C6:D6").Select
    ActiveSheet.Paste
    Windows("Master worksheet in progress v0.2.xlsm").Activate
    Range("L2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Handover Action Form.xlsm").Activate
    Range("E8").Select
    ActiveSheet.Paste
    Windows("Master worksheet in progress v0.2.xlsm").Activate
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    Range("Q2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Handover Action Form.xlsm").Activate
    Range("E6:F6").Select
    ActiveSheet.Paste
    Windows("Master worksheet in progress v0.2.xlsm").Activate
    Range("R2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Handover Action Form.xlsm").Activate
    Range("E13").Select
    ActiveSheet.Paste
    Windows("Master worksheet in progress v0.2.xlsm").Activate
    Range("S2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Handover Action Form.xlsm").Activate
    Range("A18:D20").Select
    ActiveSheet.Paste
    Windows("Master worksheet in progress v0.2.xlsm").Activate
End Sub

As you can see though, with this script I can only copy from row 3. The cells that are being pasted into "Handover Action Form" will remain the same.

I am unable to attach the two spreadsheets or upload images (work computer) but am happy to provide more info as required.

[vbfbryce]

Have both your Master and your other workbook open, then do something like this:

Sub copyVals()
    Dim wbMaster As Workbook
    Dim wbAnother As Workbook
    Dim wsMaster As Worksheet
    Dim wsAnother As Worksheet
    Dim currentRow As Long
    Dim pasteRow As Long
    
    Set wbMaster = Workbooks("masterBook.xlsm") 'change to your book's name
    Set wsMaster = wbMaster.Sheets(1)
    Set wbAnother = Workbooks("anotherBook.xlsx")   'change to your book's name
    Set wsAnother = wbAnother.Sheets(1)
    currentRow = ActiveCell.Row     'the row from which to copy
    'copy from A in Master to A in Another
    pasteRow = (wsAnother.Range("a" & Rows.Count).End(xlUp).Row) + 1    'first empty cell in column A in "Another book"
    wsMaster.Range("a" & currentRow).Copy
    wsAnother.Range("a" & pasteRow).PasteSpecial
    Application.CutCopyMode = False
    'copy from C in Master to B in Another
    wsMaster.Range("c" & currentRow).Copy
    wsAnother.Range("b" & pasteRow).PasteSpecial
    Application.CutCopyMode = False
    'copy from G in Master to C in Another
    wsMaster.Range("g" & currentRow).Copy
    wsAnother.Range("c" & pasteRow).PasteSpecial
    Application.CutCopyMode = False
End Sub

[Toothy]

Thank you for your response vbfbryce, it was helpful in finding the answer to my slightly more specific scenario. As the "Handover Action Form" was not going to be constantly updated i.e. I would copy some info from a row in the master worksheet over to specific cells in varying rows on the Handover Action Form, then print it out and hand it to another department, it meant that the paste function needed to be done to specific cells (in various rows) on the Handover Action Form.

This is what a friend and I came up with while hungover this morning:

   Application.ScreenUpdating = False
    Application.Left = -2
    Application.Top = 2.5
    Workbooks.Open Filename:="C:\Desktop\Handover Action Form.xlsm" _
        , UpdateLinks:=0
    Windows("Master worksheet in progress v0.3.0.xlsm").Activate
    Cells(ActiveCell.Row, 2).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Handover Action Form.xlsm").Activate
    Range("A8").Select
    ActiveSheet.Paste

This opens the handover worksheet, selects column B from the highlighted row on the main worksheet, then pastes it into the "Handover Action Form" cell A8.

I would prefer to make it a bit simpler without all the looping, such as the following which copies and pastes into text form fields in MS Word but alas, I am not smart enough to figure it out!

Sub Copyexcelpasteword()
    Dim oWordApp As Object, oWordDoc As Object
    Dim FlName As String
    
    FlName = "C:\Desktop\document_name.docx"
    
    On Error Resume Next
    Set oWordApp = GetObject(, "Word.Application")
    
    If Err.Number <> 0 Then
        Set oWordApp = CreateObject("Word.Application")
    End If
    Err.Clear
    On Error GoTo 0
    
    oWordApp.Visible = True
    
    Set oWordDoc = _
    oWordApp.Documents.Open(FlName)
    
    oWordDoc.FormFields("Text1").Result = Sheets("Worksheet").Cells(ActiveCell.Row, 9).Value
    oWordDoc.FormFields("Text2").Result = Sheets("Worksheet").Cells(ActiveCell.Row, 1).Value
    oWordDoc.FormFields("Text3").Result = Sheets("Worksheet").Cells(ActiveCell.Row, 3).Value
    oWordDoc.FormFields("Text4").Result = Sheets("Worksheet").Cells(ActiveCell.Row, 4).Value
    oWordDoc.FormFields("Text5").Result = Sheets("Worksheet").Cells(ActiveCell.Row, 5).Value
    oWordDoc.FormFields("Text6").Result = Sheets("Worksheet").Cells(ActiveCell.Row, 6).Value
    oWordDoc.FormFields("Text7").Result = Sheets("Worksheet").Cells(ActiveCell.Row, 7).Value
    
End Sub