Copy and pasting a range of cells from one worksheet to another

Question

I am new to VBA. I am trying to copy cells J9:J22 and cell E7 from "final output sheet" to "saved results" sheet. Below is the macro I have written. It is not showing any error but on running it the single cell E7 is getting pasted, however J9:J22 are not and it is showing blank. Can you please help?

Private Sub CommandButton1_Click()
Dim noofboxes As Range, Depot As String
Worksheets("Final Output Sheet").Select
Set noofboxes = Worksheets("Final Output Sheet").Range("J9:J22")
Depot = Range("E7")
Worksheets("Saved Results").Select
Worksheets("Saved Results").Range("A1").Select
If Worksheets("Saved Results").Range("A1").Offset(1, 0) <> "" Then
Worksheets("Saved Results").Range("A1").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Depot
ActiveCell.Offset(0, 3).Activate
ActiveCell.Value = noofboxes
End Sub

You really should avoid using `Select` like this. See [this](http://stackoverflow.com/q/10714251/445425). — chris neilsen, Dec 23 '13 at 10:08
You should use Range.Copy and Range.Paste methods to copy/paste data. — AjS, Dec 23 '13 at 10:10
Have you tried `F8` stepping through your code with the Locals window/pane active so you can see what your variables are holding? — Mark Fitzgerald, Dec 23 '13 at 10:11

score 0 · Answer 1 · answered Dec 23 '13 at 11:39

0

How about this:

Private Sub CommandButton1_Click()
    Dim target As Range
    With Worksheets("Saved Results")
        If .Range("A1").Offset(1, 0) <> "" Then
            Set target = .Range("A1").End(xlDown)
        Else
            Set target = .Range("A1")
        End If

        Worksheets("Final Output Sheet").Range("E7").Copy Destination:=target.Offset(1, 0)
        Worksheets("Final Output Sheet").Range("J9:J22").Copy Destination:=target.Offset(0, 3)
    End With
End Sub

answered Dec 23 '13 at 11:39

Alex P

12,249
5
51
70

Thanks Alex for the reply but there are a couple of problems with the code: 1) J9:J22 cells do not get pasted in front of the cell where E7 gets pasted; they get pasted one cell above it 2) When i run the macro again the new result should not overlap with the existing result but should be below it – user3129145 Dec 23 '13 at 12:43
(1) Change to `Offset(1, 3)`? (2) Not sure what you mean? – Alex P Dec 23 '13 at 12:51
I will explain 2nd query again: When i run the macro 1st time it copies the result, then if the data in the cells J9:J22 changes and I run the macro again, I want it to be pasted below the 1st result and not over it. – user3129145 Dec 23 '13 at 13:01

Copy and pasting a range of cells from one worksheet to another

1 Answers1