Programmatically reading the excel sheet name-->vbscript

[Guest]

Hi,

I have to read data from pivot table for which the source data sheet is not
known. I read the pivot cache and fetch the count and then click on the cell
to get the complete data. The data is fetched in a new sheet and
programmatically i need to know which sheet it is ( for further processing).
Code:
Sheet2.Range("B2").ShowDetail = True

Above line of code does the cell (B2) click and fetch the data into a new
sheet. I need to know in which sheet it displays the data.
(I need to perform this in my vbscript code.)

Thanks and Regards
Rishabh

 

[Jan Karel Pieterse]

Hi Rishabh,

Above line of code does the cell (B2) click and fetch the data into a new
sheet. I need to know in which sheet it displays the data.

AFAIK, this action always inserts a new worksheet to the immediate left of
the sheet the PT lives on, so:

Sheets(Sheet2.index-1) should give you that sheet.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Guest]

Hi Jan Karel Pieterse,
Thanks allot! It worked.

Regards
Rishabh