excelvbacopypaste

VBA - appending copied values to end of column

I am trying to paste a column copied from one workbook to the end of another column in another workbook. I have copied the values and have the address of where I want to paste, but I can't paste a "range" into a cell.

newIndex_key and oldIndex_key are integers of the column ID.

Public Function cpcolumn(newIndex_key, oldIndex_key)
    Dim addr As String
        
    Windows("PFMEAandCP_Assembly_External.xlsm").Activate
    Sheets("List").Select
    'Columns("D:D").Insert 'insert columns for "engine model" and old PFMEA indicator column

    'delete empty rows
    'Call deleteEmptyRows

    'find index of last row in new PFMEA sheet
    Dim last_row As Integer
    last_row = Range("B1").CurrentRegion.rows.count
    last_row = last_row + 1

    'copy
    Windows("MY21 J1 EA PFMEA & Control Plan.xlsx").Activate
    Sheets("List").Select
    Columns(oldIndex_key).Copy
    'paste

    ' HOW TO PASTE A COLUMN INTO SPECIFIC CELL AND DOWN
    Windows("PFMEAandCP_Assembly_External.xlsm").Activate
    'Sheets("List").Select
    Set pastesheet = Worksheets("List").Cells(last_row, newIndex_key)
    addr = pastesheet.Address(RowAbsolute:=False, ColumnAbsolute:=False, External:=False)
    'pastesheet.Paste.End (xlDown) '.PasteSpecial
    
    'Worksheets("List").Range (addr)
    'MsgBox (addr)
    'Worksheets("List").Range ("addr") 
End Function
Solution

  • Like this should work:

    Public Function cpcolumn(newIndex_key, oldIndex_key)
    
    Dim wsDest As Worksheet, wsSrc As Worksheet, rng As Range
        
    'no need to use Windows/Select/Activate
    Set wsDest = ThisWorkbook.Worksheets("List")
    Set wsSrc = Workbooks("MY21 J1 EA PFMEA & Control Plan.xlsx").Worksheets("List")
    
    'range to be copied (starting from row 2)
    Set rng = wsSrc.Range(wsSrc.Cells(2, oldIndex_key), _
                          wsSrc.Cells(Rows.Count, oldIndex_key).End(xlUp))
    
    'copy after last filled cell in column `newIndex_key`
    rng.Copy wsDest.Cells(Rows.Count, newIndex_key).End(xlUp).Offset(1)
  
End Function

    If your code doesn't need to return a value then it should be a Sub and not a Function