Using Worksheet Name in Code/Formula
 

First, thank you for your help.
I created a macro that is used with many different workbooks and I use the
worksheet name as criteria for one of the formulas. Is there a way to add
some code that will get the worksheet name of the open workbook and put it
into a formula or function? My workbooks contain varying amounts of
worksheets, one workbook has two sheets another has four and another has 12.
Some of the names of the sheets begin with the word summary, these sheets I
don't include in my formula. Right now I go into my macro and manually change
the criteria but this sometimes a long time. Can something like this be done?

TIA
Joe

Using Worksheet Name in Code/Formula
 

Have a look at Bob Phillips's site he

http://www.xldynamic.com/source/xld.xlFAQ0002.html

This shows how you can get filenames, sheetnames etc into a cell with
a formula. Perhaps if you had such a formula in the same cell (eg X1)
on all your sheets in all workbooks, then your macro could read the
value from that cell and thus operate automatically with different
files.

Hope this helps.

Pete

On May 28, 7:15*pm, Joe Gieder
wrote:
First, thank you for your help.
I created a macro that is used with many different workbooks and I use the
worksheet name as criteria for one of the formulas. Is there a way to add
some code that will get the worksheet name of the open workbook and put it
into a formula or function? My workbooks contain varying amounts of
worksheets, one workbook has two sheets another has four and another has 12.
Some of the names of the sheets begin with the word summary, these sheets I
don't include in my formula. Right now I go into my macro and manually change
the criteria but this sometimes a long time. Can something like this be done?

TIA
Joe

Using Worksheet Name in Code/Formula
 

This code...

ActiveWorkbook.ActiveSheet.Name

will return to the active worksheet name in the active workbook (just assign
it or concatenate it or whatever as needed). Is that what you were looking
for?

Rick


"Joe Gieder" wrote in message
...
First, thank you for your help.
I created a macro that is used with many different workbooks and I use the
worksheet name as criteria for one of the formulas. Is there a way to add
some code that will get the worksheet name of the open workbook and put it
into a formula or function? My workbooks contain varying amounts of
worksheets, one workbook has two sheets another has four and another has
12.
Some of the names of the sheets begin with the word summary, these sheets
I
don't include in my formula. Right now I go into my macro and manually
change
the criteria but this sometimes a long time. Can something like this be
done?

TIA
Joe

Using Worksheet Name in Code/Formula
 

Thanks Pete. I did look at his site and tried that but the problem is my code
creates a new worksheet called "Comments Summary" that takes all the cells on
the different sheets that contain a comment and puts them on the new sheet.
Then within the code I have a formula that gets the costs associated with the
row containing the comment.
This is an example of the data in the Comments Summary workbook.

Sheet Cell Address Invoice Comment Amount
GE PO 2051280 $L$10 9257 PDR $450,000.00
GE PO 2051280 $L$11 9258 CDR $450,000.00
GE PO 2057879 $B$8 9114 Hardware $318,500.00
GE PO 2057879 $G$8 9114 Risk Reduction $364,000.00
QQ PO 2054352 $G$9 NMST045274 Survey $35,617.00
QQ PO 2054352 $B$9 NMST045134 TIMs $550,431.70

This is the code with the formula I always change to get the summary costs
for each PO.
Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(--(R[3]C[-5]:R[498]C[-5]=""GE PO
2051280""),R[3]C:R[498]C)"
Range("F3").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(--(R[2]C[-5]:R[497]C[-5]=""GE PO
2057879""),R[2]C:R[497]C)"
Range("F4").Select
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT(--(R[1]C[-5]:R[496]C[-5]=""QQ PO
2054352""),R[1]C:R[496]C)"


Is there maybe an easier way to get the totals for the POs?
Thanks for you help.

Joe


"Pete_UK" wrote:

Have a look at Bob Phillips's site he

http://www.xldynamic.com/source/xld.xlFAQ0002.html

This shows how you can get filenames, sheetnames etc into a cell with
a formula. Perhaps if you had such a formula in the same cell (eg X1)
on all your sheets in all workbooks, then your macro could read the
value from that cell and thus operate automatically with different
files.

Hope this helps.

Pete

On May 28, 7:15 pm, Joe Gieder
wrote:
First, thank you for your help.
I created a macro that is used with many different workbooks and I use the
worksheet name as criteria for one of the formulas. Is there a way to add
some code that will get the worksheet name of the open workbook and put it
into a formula or function? My workbooks contain varying amounts of
worksheets, one workbook has two sheets another has four and another has 12.
Some of the names of the sheets begin with the word summary, these sheets I
don't include in my formula. Right now I go into my macro and manually change
the criteria but this sometimes a long time. Can something like this be done?

TIA
Joe

Using Worksheet Name in Code/Formula
 

Thanks for your help Rick. I added a little more detail under Pete's reply. I
hope I clarrified things better.

Joe


"Rick Rothstein (MVP - VB)" wrote:

This code...

ActiveWorkbook.ActiveSheet.Name

will return to the active worksheet name in the active workbook (just assign
it or concatenate it or whatever as needed). Is that what you were looking
for?

Rick


"Joe Gieder" wrote in message
...
First, thank you for your help.
I created a macro that is used with many different workbooks and I use the
worksheet name as criteria for one of the formulas. Is there a way to add
some code that will get the worksheet name of the open workbook and put it
into a formula or function? My workbooks contain varying amounts of
worksheets, one workbook has two sheets another has four and another has
12.
Some of the names of the sheets begin with the word summary, these sheets
I
don't include in my formula. Right now I go into my macro and manually
change
the criteria but this sometimes a long time. Can something like this be
done?

TIA
Joe