Create variable numbers of buttons based on size of data set, add macros to each button...? XL07

[Paaqua]

I have tried searching for an answer to this in many ways, but can't seem to find a workable solution.

I have a single column data set that I need to divide into smaller chunks for input into another program. The size of this data set changes over time (gets larger). I am trying to build a button that will run code (so anyone can use it) that creates buttons to select and copy non-overlapping ranges of the data set. The trick is that as the data set increases in size, the number of buttons will need to increase.

So far I have found code to create a variable number of buttons based on the size of the data set, but I need to assign slightly different code to each button so that they select different ranges of cells.

​

Private Sub CommandButton1_Click()
Dim groups As Integer, repeat As Integer
groups = Application.RoundUp((Cells(Rows.Count, 1).End(xlUp).Row) / 9000, 0)
For repeat = 1 To groups
   ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, Left:=21, Top:=6.75 + (repeat * 24), Width:=82, Height:=24).Select
Set shp = ActiveSheet.Shapes(Selection.Name)
   With shp.OLEFormat.Object
        .Object.Caption = "Copy range" & Str(repeat)
    End With
Next repeat
End Sub

This gives me enough buttons and gives them unique, descriptive captions of what I want them to do, but I want each button so created to further run code similar to:

Dim fromRow As Integer, toRow As Integer
fromRow = Range("D3").Value
toRow = Range("E3").Value
Range(Cells(fromRow, 1), Cells(toRow, 1)).Copy

but where fromRow and toRow ranges are generated dynamically.

As you may be able to guess from that code, right now I have cells in the table that calculate my from and to rows for the specific number of ranges that I currently require. I've been thinking that I might be able to use a macro to generate those ranges in cells using a While loop, but that doesn't really solve my problem of having separate buttons to call those ranges.

Thank you in advance for any help on this problem.