VBA copy range to another range in next empty cell

Question

Rng1.Copy Destination:=Worksheets("RefindData").Range(Destination)

Where Rng1 is the range of data to be copied and Destination is currently a cell reference (E.g B2)

This code will be called multiple times. How can I alter this so that the destination is the same column (E.g column B) but the row is the next empty cell?

E.g so on the first call, B2 onwards is where the values are copied to, then on the next call the next empty cell after the first call is where the second call should start outputting its values. Then the next empty cell for the start of the third call, and so on.

I can alter the Destination variable to just state column letter if something like this:

Rng1.Copy Destination:=Worksheets("RefindData").Range(Destination & ???)

Is along the right lines?

Tehscript · Answer 1 · 2017-05-29T19:20:06.120

3

Sub CopyPasteCells()
Dim Rng1 As Range, Rng2 As Range, ws As Worksheet

Set ws = Worksheets("RefindData")
Set Rng1 = ws.Range("C2:C10") 'Copy range as you like
Set Rng2 = ws.Range("B" & ws.Rows.Count).End(xlUp).Offset(1, 0) 'Paste range starting from B2 and then first empty cell

Rng1.Copy Destination:=Rng2 'Copy/Paste
End Sub

edited May 29 '17 at 19:20

answered May 29 '17 at 18:43

Tehscript

2,556
2
13
23

I would recommend that you do not hardcode the values. You may want to see [This](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) – Siddharth Rout May 29 '17 at 18:45
It is just an old habit, you can use max row number allowed as 1048576 – Tehscript May 29 '17 at 18:49
You are missing the point. using hardcoded values is not the right thing to do :) – Siddharth Rout May 29 '17 at 18:51
No I was just answering your first comment. For your second comment, thank you for pointing out this detailed post about last used cell. The method I use does exactly what OP wants, even after formatting cells. Can you show the wrong part? – Tehscript May 29 '17 at 19:11
1

`Can you show the wrong part?` If you hardcode the `Rows.Count` with `65536` or `1048576` then you are limiting the file to specific office versions. If you use `1048576` then your code will give error in Excel 2003. If you use `65536` in Excle 2007+ then you will not get the correct last row if there is data in any cell beyond `65536` and hence one should avoid using hardcoded values. you can replace `65536` or `1048576` with `Rows.Count` :) See my code example on the usage of `Rows.Count` – Siddharth Rout May 29 '17 at 19:16
`ws.Range("B1048576")` can be written as `ws.Range("B" & ws.Rows.Count)` – Siddharth Rout May 29 '17 at 19:19
Well I have never thought this would be a problem since I know what the total number of rows are supposed to be, and the version of excel. In any case I edited my code, thanks. – Tehscript May 29 '17 at 19:22
It becomes a problem when you send the file to someone who doesn't have the same version as yours ;) – Siddharth Rout May 29 '17 at 19:23
1

@Siddharth Rout Small details :) Thanks – Tehscript May 29 '17 at 19:26

score 2 · Answer 2 · edited May 29 '17 at 19:59

You can also try something like code below.

Assumptions:

Active cell is in the column, where you want to paste the results (you want to paste results in column B -> select cell from B column [for example B2],
The first row is filled with headers, so the results gonna be pasted from second row

Code

Sub CutCopyPaste()
    Dim lngCol As Long
    Dim rngCopy As Range

    Set rngCopy = Range("A1") 'The cell which ic copied

    lngCol = Selection.Column 'active column where the results will be pasted

    On Error Resume Next
    rngCopy.Copy Cells(Cells(1, lngCol).End(xlDown).Row + 1, lngCol)

    If Err.Number = 1004 Then
        MsgBox "Be sure that active cell is in the column, where the results should be pasted!" & vbNewLine & vbNewLine & "Try again"
        Err.Clear
    End If
End Sub

score 1 · Accepted Answer · answered May 29 '17 at 18:38

1

You mean like this?

Sub Sample()
    Dim rng1 As Range
    Dim wsO As Worksheet

    Set wsO = Worksheets("RefindData")
    Set rng1 = Range("A1:A10")

    rng1.Copy Destination:=wsO.Range("B" & _
                                     wsO.Range("B" & wsO.Rows.Count).End(xlUp).Row + 1)
End Sub

Every time you run the macro it will paste in the next available row after the last row.

answered May 29 '17 at 18:38

Siddharth Rout

147,039
17
206
250

I get `1004 - Application defined or object defined error` for the last line `Rng1.Copy Destination:=wsRefind.Range(Destination & wsRefind.Range(Destination & wsRefind.Rows.Count).End(xlUp).Row + 1)` – Aurelius May 29 '17 at 20:30
`Dim wsRefined As Worksheet` and `Set wsRefind = wb.Worksheets("RefindData")` also present – Aurelius May 29 '17 at 20:31
1

What is the value of `Destination`? – Siddharth Rout May 29 '17 at 20:33
Ignore that it was something not commented out properly. This now works, thanks! I have combined it with a little of Tehscript answer as well by setting a `rng2` and then using using `rng1.Copy Destination:=rng2` :) – Aurelius May 29 '17 at 20:52

VBA copy range to another range in next empty cell

3 Answers3