CODEWITHSUNDEEP
vbaexcel
ln29st
ln29st

Reputation: 83

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!

Upvotes: 0

Views: 2511

Answers (2)

user8244044
user8244044

Reputation:

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

Upvotes: 0

Karl Kristjansson
Karl Kristjansson

Reputation: 328

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

Upvotes: 1

Related Questions