Copy a block with a button Repeatedly 1

[Scott24x7]

Hi All,
It's been a while since I had to do any work in Excel and VBA as I try to avoid macros as much as possible and work in-cell, but today I hit one of those moments, I know I have to go to VBA, and I can't seem to figure out the solution.

I have added a button on my form call "Add Entry". I have a range of cells that I want to copy the contents of to one space below the entry block, and paste it there. But the catch is, I need to be able to do that multiple times, without over writing the data.

So I need to copy the contents of B8:I13 (skip the next row 14) into the cells starting at B15. But if I press the "Add Entry" again, the next time the button should copy the contents of B8:I13 and place them (skip a row) starting at B22, then B30, B38, etc.

I tried to record a macro to do it, but it only works once (i.e. it will let me copy the data from B8:I13 into B15:I20, but if I press the button again, I have no way to tell where the "last" data was entered, and put the block after it.

I hope I'm making sense. The current code looks like this:

Sub Button1_Click()
    Range("B8:I13").Select
    Selection.Copy
    Range("B15").Select
    ActiveSheet.Paste
End Sub

Any help?


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[SkipVought]

hi,

Very specific requirements.

Sub Button1_Click()
    Dim lRow As Long

    With Range("B8:I13")
       .Copy
       lRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row + 2

       Cells(lRow, "B").PasteSpecial xlPasteAll
    End With
End Sub

 

[SkipVought]

Notice, there are no Select methods used here.

 

[Scott24x7]

Skip,
As always, you have been my hero for more than a decade now. Really appreciate the help on this one. Worked like a charm.
Cheers,

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."