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

[sschwant]

I have a process that requires summing the same range of cells across several worksheets within a macro. The formula and logic for the sum function looks like:


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. As described above.

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

 

[NARAYANK991]

Hi ,

The Copy method for worksheet(s) copy , has a parameter labelled After ; thus to ensure that the copied worksheet is located after a designated worksheet. Thus , if you specify the After worksheet as the left bookend tab , you are guaranteed that the copied worksheet will not be left out of your calculations.

Preferably , when you specify your worksheets in the code , use the worksheet CodeName rather than the worksheet name , so that there is a lesser chance of your code failing because of users renaming the worksheets.

As far as capturing the selected name from the DV dropdown is concerned , the selected name will be in the cell which has the DV dropdown ; you can simply access the cell contents.

Probably if you can upload your workbook , it will make it easier to give a better solution / answer.

Narayan

 

[sschwant]

T

Hi ,

The Copy method for worksheet(s) copy , has a parameter labelled After ; thus to ensure that the copied worksheet is located after a designated worksheet. Thus , if you specify the After worksheet as the left bookend tab , you are guaranteed that the copied worksheet will not be left out of your calculations.

Preferably , when you specify your worksheets in the code , use the worksheet CodeName rather than the worksheet name , so that there is a lesser chance of your code failing because of users renaming the worksheets.

As far as capturing the selected name from the DV dropdown is concerned , the selected name will be in the cell which has the DV dropdown ; you can simply access the cell contents.

Probably if you can upload your workbook , it will make it easier to give a better solution / answer.

Narayan

Thank your for the reply. Can you expound on the "code name" vs. the "sheet name"?

Thanks,

Steve

 

[NARAYANK991]

Hi Steve ,

Just open a new workbook , and go into the Visual Basic Editor ; in the left hand side pane , which is known as the Project Explorer , you should see the worksheets listed , as follows :

Sheet1 (Sheet1)

If you click on this , and then click on the View menu option , and select Properties Window , you should see a complete list of all the worksheet properties. You will find two properties related to the worksheet name ; one of them has its label enclosed in brackets , as in (Name) , while the other is just Name. Contrarily , the label (Name) refers to the name Sheet1 which is not in brackets , while the label Name refers to the name Sheet1 which is enclosed in brackets.

If all this is confusing , just go to the worksheet itself , change the tab name , and then come back to the VBE and see how the change is reflected.

With a little experimenting , you should be able to understand exactly what is going on.

To summarise , the name Sheet1 which is not in brackets ( which is on the left ) is the CodeName , while the name Sheet1 which is enclosed in brackets ( which is on the right ) is the Name.

Once you have made changes to the names , either in the worksheet or through the Properties window , you can type in the Immediate window :

?Worksheets(1).Name

?Worksheets(1).CodeName

and see what is displayed.

Remember , the CodeName can only be changed through the Properties window , and therefore is less likely to be changed by the lay user.

Narayan