Is it possible (or necessary) to name two worksheets as a range w/in a macro?

[sschwant]

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

[davesexcel]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[mikerickson]

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?

Code like this.

Sheets("PCTab1").Copy Before:=Sheets("RightBookend")

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?

Code like this would be altered to reflect the correct sheet name and cell location of the cell with validation. Duplicate names have to be avoided.

ActiveSheet.Name = Sheets("PCTab").Range("A2")

[dguillett36]

Use this with sheet named summary and no bookends
Option Explicit
Sub Sumitup()
Dim mysum As Long
Dim sh As Worksheet
Dim x As Long

Sheets("Summary").UsedRange.Offset(1).ClearContents
mysum = 0
With Sheets("Summary")
For Each sh In Sheets
If sh.[b5] > 0 Then
x = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Range("a" & x) = sh.Name
.Range("b" & x) = sh.[b5].Value
mysum = mysum + sh.[b5].Value
End If
Next sh
.Range("b" & x + 1) = mysum
End With
End Sub