CODEWITHSUNDEEP
vbaexcel
Hayley Watson
Hayley Watson

Reputation: 13

Excel: Copy Dynamic Range from one worksheet to another

I am trying to copy a dynamic range (B12:Lxx)from one worksheet to another. I need the range being copied to stop at the first empty row (there is additional data further down the sheet which I don't want copied).

I am a very basic VBA user so if you could explicitly set out your instructions that would be handy.

I have trawled through other articles but have failed to find anything that I could use.

Thanks, Hayley

Upvotes: 1

Views: 3924

Answers (2)

ib11
ib11

Reputation: 2558

Place a command button from the Forms toolbar on your "MyKPIs" sheet.

Then add this code:

Sub Button1_Click()

    Dim myrange

    Set myrange = Sheets("MyKPIs").Range("B12:L12")
    myrange.Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy Worksheets("Month Template").Range("B5")

End Sub

Considering all cells in column B have data, this will copy all the cells in the range. It will stop at the first empty cell in column B. This should help you to start.

Upvotes: 0

luckyguy73
luckyguy73

Reputation: 1939

this will work. insert a command button on your worksheet. double click the button. paste in this code between sub and end sub.

Worksheets("MyKPIs").Range("b12").CurrentRegion.Copy Worksheets("Month Template").Range("b5")

it should look like this when you are through. then go to your worksheet on developer tab toggle off design mode then click the button.

Private Sub CommandButton1_Click()

Worksheets("MyKPIs").Range("b12").CurrentRegion.Copy Worksheets("Month Template").Range("b5")

End Sub

for those inexperienced with currentregion please look at the 2 samples below that have blank cells but the region is selected and you can easily see the beginning and ending points in the range and how an entire blank row or column forms the range.

currentregion sample1

currentregion sample2

Upvotes: 1

Related Questions