Formula referencing to uncreated worksheets through code

[SoccerDad]

I have a worksheet that contains a form calculating to a total. This
total is referenced by formula on another worksheet within the same
workbook. My problem is this: several of these forms may be needed, it
is not known in advance how many will be needed and the totals from
each of these forms will need to be totaled in the formula on the
other worksheet.

I have created a macro that will allow the user to copy the worksheet
form over the necessary number of times through executing a "Do Until"
loop using a counter variable and a variable assigned to the value
entered by the user through an inputbox. I can create a formula
through code, but it would require knowing the sheet names and since
the number of sheets is unknown before the user defines it, this would
not be possible.

I have come up with a rather cumbersome solution using a "Select Case"
construct, but I was wondering if anyone had an easier and shorter
coding solution?

 

[Tom Ogilvy]

Put in two dummy sheets named

Start
End

then, in your summary sheet put in a formula like

=Sum(Start:End!A1)

Add or move any sheets you want included in the sum to positions between
sheets Start and End in the tab order.