excel - Copy Range Sheet1 Paste in Active Cell Sheet 2

I'm trying to accomplish the following:

1. Sheet1: User clicks a button that runs VBA.




2. Sheet2: VBA selects the first non-blank cell on column D

   
   
   

   Range("D" & Rows.Count).End(xlUp).Offset(1).Select




3. Sheet1: VBA copies a range of values

   
   
   

   Worksheets("Sheet1").Range("E2:AJ11").Copy





4. Sheet2: VBA pastes the copied range into the active cell selected previously.

This part where I try pasting into the active cell is not working:

Worksheets("Results").ActiveCell.PasteSpecial xlPasteValues

Does anyone know how to fix this?

Thanks much!

Answer

Your code could use some refining, but to keep your code and make it work I would change the order of commands to this and see if that works for you.

1. Sheet1: User clicks a button that runs VBA.


2. Sheet1: VBA copies a range of values


3. Worksheets("Sheet1").Range("E2:AJ11").Copy

then to sheet 2

1. Sheet2: VBA selects the first non-blank cell on column D


2. Range("D" & Rows.Count).End(xlUp).Offset(1).Select (or just paste instead of select)


3. Sheet2: VBA pastes the copied range into the active cell selected previously.

or just skip the line that is bothering you and instead use:

Range("D" & Rows.Count).End(xlUp).Offset(1).pastespecial xlpastevalues

(adjust as you need, this is for values)

Or use something like this:

Sub Copy()

Dim LastRow As Long

Dim Results As Worksheet

    Set Results = Sheets("Results")
    LastRow = Results.Cells(Results.Rows.Count, "D").End(xlUp).row

    Range("E2:AJ11").Copy
    Results.Range("D" & LastRow + 1).PasteSpecial xlPasteAll

    Application.CutCopyMode = False
End Sub