Copy Range Sheet1 Paste in Active Cell Sheet 2

Question

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 1

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

Answer 2

Try this one.

Dim i as Integer
i = 1

Do while cells(i,4) <> ""
    Range("D" & Rows.Count).End(xlUp).Offset(1).Select
    Worksheets("Sheet1").Range("E2:AJ11").Copy
    i = i + 1
Loop