0

Can't paste info to first empty cell

Tried a lot of different options found online, can't get it right for my situation.

Sub cop()

Dim LastRow As Long
Dim x As Range

Sheets("1").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
Sheets("Data Validation").Select
Range("A2").Select
ActiveSheet.Paste

'finds the last row in the Holdings report worksheet
LastRow = ActiveSheet.Cells.find(What:="*", After:=ActiveSheet.Cells(1, 
1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, 
MatchCase:=False).Row


x = (LastRow + 1)

Sheets("2").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
Sheets("Data Validation").Select
Range(x, 1).Select
ActiveSheet.Paste


End Sub

I want to copy from worksheet '2', and place the data on column A in the Data Validation sheet, at the first row that is blank (underneath the data that was previously pasted there from worksheet '1'.

Lukec
  • 73
  • 1
  • 12
  • 7
    You should read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – SJR Oct 01 '19 at 16:01
  • @ SJR thank you! Big help. – Lukec Oct 01 '19 at 16:07
  • Declare `x as Long` (though I don't think you need it at all) and syntax dictates that you need `Cells(x, 1)` rather than `Range`. – SJR Oct 01 '19 at 16:08

0 Answers0