VBA code to copy and paste a dynamic range

Question

Hello,

I have data on a work sheet that I like to copy and paste to another worksheet. The number of being copy rows varies and is determined in cell A1. For example, in the enclosed screenshot, the data range that needs to be copied is B1:G38. My question is how do I reference the row number 38(G38) in VBA, so the correct data will be copied and pasted next time when the number of rows is determined as say 380 or 3800?

Any help on this will be very much appreciated.

Phongski

Answer 1

Try this macro after entering a number in A1 of Sheet1

Sub findbottom_paste()

    Dim rng1 As Range

    Dim n  As Long

    n = Sheet1.Range("A1").Value

    Set rng1 = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) _

            .Offset(1, 0)

    Sheets("Sheet1").Range("B1:G" & n).Copy _

            Destination:=rng1

End Sub

Gord

Answer 2

It is not clear to me what you are asking.

In general, you would use the 38 in place of the Row argument by what ever method you are setting the Range object.

eg:  (all equivalent)

   Range("B1:G & 38)

   Range("B1", Cells(38, "G"))

   Range(Cells(2,1), cells(38,7))

Answer 3

Thank you, Gord.

Your original code did not work with the pasting code below.

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

However, after removing "Destination:=rng1" everything worked just fine. I have no idea why though.

Thanks again,

Phongski