Using Worksheet Name in Code/Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe Gieder - 28 May 2008 19:15 GMT

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

Reply to this Message

Pete_UK - 28 May 2008 19:29 GMT

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

On May 28, 7:15 pm, Joe Gieder <JoeGie...@discussions.microsoft.com>
wrote:

> First, thank you for your help.
> I created a macro that is used with many different workbooks and I use the
[quoted text clipped - 8 lines]
> TIA
> Joe

Reply to this Message

Joe Gieder - 28 May 2008 19:56 GMT

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

> Have a look at Bob Phillips's site here:
>
[quoted text clipped - 24 lines]
> > TIA
> > Joe

Reply to this Message

Rick Rothstein (MVP - VB) - 28 May 2008 19:49 GMT

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

> First, thank you for your help.
> I created a macro that is used with many different workbooks and I use the
[quoted text clipped - 12 lines]
> TIA
> Joe

Reply to this Message

Joe Gieder - 28 May 2008 19:58 GMT

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

Joe

> This code...
>
[quoted text clipped - 22 lines]
> > TIA
> > Joe

Reply to this Message