0

I have been racking my brain over this code. I am trying to copy a set of data from table "CST" to the end of a currently populated table titled "HT_Table" and input the type of test that was run for the entries I am pasting. The historical data table should have a running list of entries.

Each time I run the macro I want to copy "[[Social Security Number]:[Rehire Date]]" into the bottom of a different table that has a running list of selections and add "Loan" to the right of it under the "Test" column.

Sub Copy_Data

'Copy critical data
     Dim tbl As ListObject
     Set tbl = CST.ListObjects(1)
     Range(tbl & "[[Social Security Number]:[Rehire Date]]").Copy   

     Windows("Historical Testing.xlsx").Activate

'I named the column in the table I am pasting to "SSN" 

    Range("SSN").End(xlDown).Select
    ActiveCell.Offset(1, 0).PasteSpecial
    Dim PY As Worksheet
    Set PY = ActiveSheet
    With PY.ListObjects("HT_Table")

    .ListColumns("Test").Range.Select
    ActiveCell.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)) = "Loan"
    End With
End Sub

I run into problems with the Selection.End(x1Down)) if there is only one row pasted to the bottom of the table. The above code selects the one row and then highlights all the way to the end of the worksheet.

CokieRoberts
  • 1
  • 1
    It's highly suggested to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/) as it can cause some confusion/errors. I suggest reading through that and following the advice...however, for now, try going up instead: `Range(Selection, Range(Cells(Selection.Row,Selection.Column),Cells(Rows.Count,Selection.Column)).End(xlUp)) = "Loan"` ? – BruceWayne Jun 08 '17 at 21:27
  • 1
    @BruceWayne - the brackets don't match, but that may not work anyway - I've had problems before where an `End(xlUp)` stops at the last row of the table, not the last used cell in the table. (It's worth a try though) – YowE3K Jun 08 '17 at 21:30
  • @BruceWayne Thanks for the tip. How would you take out this: ' Range("SSN").End(xlDown).Select ActiveCell.Offset(1, 0).PasteSpecial' ? Is there a way to reference the table and paste whatever I have copied to below what is already in the table? – CokieRoberts Jun 08 '17 at 21:40
  • @YowE3k That is the issue that I am running into with BryceWayne's code and others in that vein. It selects all blank rows in the column, but it also selects the next cell that is filled. – CokieRoberts Jun 08 '17 at 21:44

0 Answers0