I have a process that will require summing the same range of cells across several worksheets within a macro. The formula for the sum function on the summary worksheet is no problem, the logic is here:

Suppose you have twelve worksheets named January through December. You want to sum the total of cell A2 of all these worksheets on a sheet called Summary.

First, make sure your Summary worksheet is not amidst the others. In other words, make sure it appears at the far right or far left of the monthly worksheets. Whatever you do, you'll want all the worksheets you're summing to be side-by-side, with no "foreign" worksheets in between.
Then, just write your formula as shown below.
=SUM(January:December!A2)
Suppose your worksheets aren't in any kind of sequential order. Or you have 50 worksheets, and you only want to sum 30 of them, but you're constantly adding worksheets, so the beginning and end worksheet names may change?In this case, place a blank worksheet to the left of the first worksheet you want to include in the formula. Call that worksheet "First". Insert another blank worksheet after the last worksheet you want to include in the formula. Call that worksheet "Last". You can even hide these worksheets!Then, your formula will be:
=SUM(First:Last!A2)
What if you add another worksheet between the first and last worksheet you use? Nothing! It'll add the cells in the new worksheet, too. If you don't want the values of a certain worksheet added, keep the worksheet outside of between the first and last worksheets you use in your formula.


The macro will take a working copy of a pricing calculator call it "PCTAB1" and clone it - copy/paste - such that a new pricing scenario can be created for a different product type.

The idea is to have a "dummy worksheet1" and a "dummy worksheet2" which would be the book-ends for the worksheets that need to be summed - which would be placed in-between the bookends.

So, we'll have a summation tab on the right side of the workbook summing up worksheets between the book ends.

My question is: how do I ensure that the macro to copy "PCTAB1" will place the copy into the range between the bookends so the formula on the summary worksheet is not broken?

Also, let's say I have a data validation drop down for ProductName ... how can I capture that selected product name to rename the new worksheet?


Thanks for any advice!!

sschwant