change range a table appears every time a button within a userform is clicked

**mcdermott2** · 04-11-2013, 03:57 PM

Hello,

I have a userform with a button that, when clicked, filters sheet1 based on listbox responses, opens up a new sheet and pastes in a table based on filtered values from sheet1. The following works fine if I only want to create one table, but I would like to be able to click the button hundreds of times and have hundreds of tables added one after the other with an empty row between them.
So, for instance, the first time the button is clicked a table spanning A1:E2 would be created based on certain filtered results on sheet1. The second time the button is clicked, a new filter would be created (this is not a problem, my code works for the filter), and I would like a new table spanning A4:E5 based on the new filter, and so forth ad infinitum.

Private Sub CommandButton13_Click()

'... data is filtered

'mean, n, min, max, st. dev of filtered data is entered into a table
    Sheets.Add After:=Sheets(Sheets.Count)
    Range("A2").FormulaR1C1 = "=SUBTOTAL(1,sheet1!RC[4]:R[19998]C[4])"
    Range("B2").FormulaR1C1 = "=SUBTOTAL(9,sheet1!RC[4]:R[19998]C[4])"
    Range("C2").FormulaR1C1 = "=SUBTOTAL(5,sheet1!RC[2]:R[19998]C[2])"
    Range("D2").FormulaR1C1 = "=SUBTOTAL(4,sheet1!RC[1]:R[19998]C[1])"
    Range("E2").FormulaR1C1 = "=SUBTOTAL(7,sheet1!RC:R[19998]C)"
    Range("A1").FormulaR1C1 = "Mean"
    Range("B1").FormulaR1C1 = "N"
    Range("C1").FormulaR1C1 = "Min"
    Range("D1").FormulaR1C1 = "Max"
    Range("E1").FormulaR1C1 = "St. Dev."


'when I click the button a second time, sheet1 is filtered by other things. I then would like to use this new filtered data for the second table that would look like:
    Range("A5").FormulaR1C1 = "=SUBTOTAL(1,sheet1!RC[4]:R[19998]C[4])"
    Range("B5").FormulaR1C1 = "=SUBTOTAL(9,sheet1!RC[4]:R[19998]C[4])"
    Range("C5").FormulaR1C1 = "=SUBTOTAL(5,sheet1!RC[2]:R[19998]C[2])"
    Range("D5").FormulaR1C1 = "=SUBTOTAL(4,sheet1!RC[1]:R[19998]C[1])"
    Range("E5").FormulaR1C1 = "=SUBTOTAL(7,sheet1!RC:R[19998]C)"
    Range("A4").FormulaR1C1 = "Mean"
    Range("B4").FormulaR1C1 = "N"
    Range("C4").FormulaR1C1 = "Min"
    Range("D4").FormulaR1C1 = "Max"
    Range("E4").FormulaR1C1 = "St. Dev."

' and so forth ...

End Sub

So, if I could create a variable that increases by 3 each time I click the button, and then use that variable to define the ranges where I want the data posted, I think I would be in business. Problem is, I don't know how to go about this.

Thanks!

**mcdermott2** · 04-11-2013, 05:07 PM

Sorry! I made some mistakes with my original post. I came up with a solution on my own, which includes fixes to the above:

Sheets("Sheet8").Select
Dim l As Long
Dim m As Long

m = 1
  For l = 1 To 100 Step 3
       If Range("A" & l) = "" Then
       m = m + 1
       If m = 2 Then
    Range("A" & l + 1).Formula = "=SUBTOTAL(1,sheet1!E2:E20000)"
    Range("B" & l + 1).Formula = "=SUBTOTAL(9,sheet1!F2:F20000)"
    Range("C" & l + 1).Formula = "=SUBTOTAL(5,sheet1!E2:E20000)"
    Range("D" & l + 1).Formula = "=SUBTOTAL(4,sheet1!E2:E20000)"
    Range("E" & l + 1).Formula = "=SUBTOTAL(7,sheet1!E2:E20000)"
    Range("A" & l + 1, "E" & l + 1).Copy
    Range("A" & l + 1, "E" & l + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("A" & l).FormulaR1C1 = "Mean"
    Range("B" & l).FormulaR1C1 = "N"
    Range("C" & l).FormulaR1C1 = "Min"
    Range("D" & l).FormulaR1C1 = "Max"
    Range("E" & l).FormulaR1C1 = "St. Dev."
       End If
       End If
  Next

I imagine there are more elegant solutions out there, but this seems to work