Creating a worksheet to control which cells a macro sums

**luke.guthrie** · 06-06-2012, 03:39 PM

Is there anyway I could create a sheet in my workbook that allows the user to change the range of cells used in a macro, rather than manually editing the code.

For example, entering something 30:90 in a separate sheet would change the code from 53:86 to the new rows entered.

HTML Code:

**JieJenn** · 06-06-2012, 03:50 PM

This is just an example. The code will allows user to select a different range if desired.

Dim DataRng As Range

If MsgBox("Change Reference?", vbQuestion + vbYesNo, "Change Range") = vbYes Then
Set DataRng = Application.InputBox("Select Data Range", "Data Range", , , , , , 8)
MsgBox DataRng.Address
End If

**tlafferty** · 06-06-2012, 04:00 PM

Sure - put 2 named ranges called RowRef1 and RowRef2 in your spreadsheet where the user will make the row changes. Define 2 new variables in your code: strRowRef1 and strRowRef2. Set the strRowRef variables equal to the the named ranges.

 Dim strRowRef1 As String, strRowRef2 As String
 strRowRef1 = Application.Range("RowRef1").Value
 strRowRef2 = Application.Range("RowRef2").Value
 
 With ws1
        .Range("C53:C86").AutoFilter Field:=1, Criteria1:="=*Revenue 1200*"
    End With
    With ws2
        ActiveSheet.Range("C6").Formula = "=SUBTOTAL(109,Detail!E" & strRowRef1 & ":E" & strRowRef2 & ")"

**luke.guthrie** · 06-07-2012, 09:58 AM

JieJenn what should I be typing in to change the range.

(Ie i want it to use rows 60:70 rather than the ones in the code.)

I have tried entering C60:70