Using Worksheet Name in Code/Formula

[Joe Gieder]

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

 

[Pete_UK]

Have a look at Bob Phillips's site here:

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[Joe Gieder]

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

Joe