CODEWITHSUNDEEP
vbaexcel
Aurelius
Aurelius

Reputation: 485

VBA copy range to another range in next empty cell

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?

Upvotes: 0

Views: 3943

Answers (3)

SuShuang
SuShuang

Reputation: 190

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

Upvotes: 2

Tehscript
Tehscript

Reputation: 2556

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

Upvotes: 3

Siddharth Rout
Siddharth Rout

Reputation: 149287

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.

Upvotes: 1

Related Questions