Macro is not working

Question

I don't know why my macro is not working . Let me explain you what the macro does:

If the user type any value on any cell below column R a pivot table will be created automatically and it will move the value "Ref #" to row Labels and then from cell A2 to the last one it will copy that information and paste it on another Sheet called "Import File".

The problem is that is not copying the information from A2 to the last cell with data . I believe the error is on this part of the macro

ActiveSheet.Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Import File").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues

Answer 1

OK, First thing I would try is cleaning up some of the code and getting rid of xldown.

Please replace

ActiveSheet.Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Import File").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues

with this single line of code

Sheets("Import File").range("A1:A1").resize(Range("A" & Rows.Count).End(xlUp).Row,1) = Range("A" & Range("A" & Rows.Count).End(xlUp).Row)