| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table..getting a list of all, sourec odata and sheets locate
Can some one help me determine the sheet name that each pivot table is located
I'm using the code below thanks Sub ListPivotTablesNSource() Dim PT As PivotTable Dim Sh As Worksheet Dim lSh As Worksheet 'Loop Sheet Set Sh = Worksheets.Add Sh.Range("A1") = "Pivot Table Name" Sh.Range("B1") = "Pivot Table Source" Sh.Range("C1") = "Pivot Table Location" For Each lSh In ActiveWorkbook.Worksheets For Each PT In lSh.PivotTables With Sh.Range("A" & Sh.Rows.Count).End(xlUp) .Offset(1, 0) = PT.Name .Offset(1, 1) = PT.SourceData End With Next Next Columns("A:B").EntireColumn.AutoFit End Sub -- Helping Is always a good thing |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table..getting a list of all, source data and sheets locate
Hi
Isn't it just ..Offset(1,2) = Sh.name -- Regards Roger Govier "QuietMan" wrote in message ... Can some one help me determine the sheet name that each pivot table is located I'm using the code below thanks Sub ListPivotTablesNSource() Dim PT As PivotTable Dim Sh As Worksheet Dim lSh As Worksheet 'Loop Sheet Set Sh = Worksheets.Add Sh.Range("A1") = "Pivot Table Name" Sh.Range("B1") = "Pivot Table Source" Sh.Range("C1") = "Pivot Table Location" For Each lSh In ActiveWorkbook.Worksheets For Each PT In lSh.PivotTables With Sh.Range("A" & Sh.Rows.Count).End(xlUp) .Offset(1, 0) = PT.Name .Offset(1, 1) = PT.SourceData End With Next Next Columns("A:B").EntireColumn.AutoFit End Sub -- Helping Is always a good thing |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table..getting a list of all, source data and sheets loc
NBo...that would only get me the name of the sheet that i'm listing the pivot
table information on...I need the name of the sheet where the pivot table located -- Helping Is always a good thing "Roger Govier" wrote:. Hi Isn't it just .Offset(1,2) = Sh.name -- Regards Roger Govier "QuietMan" wrote in message ... Can some one help me determine the sheet name that each pivot table is located I'm using the code below thanks Sub ListPivotTablesNSource() Dim PT As PivotTable Dim Sh As Worksheet Dim lSh As Worksheet 'Loop Sheet Set Sh = Worksheets.Add Sh.Range("A1") = "Pivot Table Name" Sh.Range("B1") = "Pivot Table Source" Sh.Range("C1") = "Pivot Table Location" For Each lSh In ActiveWorkbook.Worksheets For Each PT In lSh.PivotTables With Sh.Range("A" & Sh.Rows.Count).End(xlUp) .Offset(1, 0) = PT.Name .Offset(1, 1) = PT.SourceData End With Next Next Columns("A:B").EntireColumn.AutoFit End Sub -- Helping Is always a good thing |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table..getting a list of all, source data and sheets loc
Sorry, I meant Ish.name
-- Regards Roger Govier "QuietMan" wrote in message ... NBo...that would only get me the name of the sheet that i'm listing the pivot table information on...I need the name of the sheet where the pivot table located -- Helping Is always a good thing "Roger Govier" wrote:. Hi Isn't it just .Offset(1,2) = Sh.name -- Regards Roger Govier "QuietMan" wrote in message ... Can some one help me determine the sheet name that each pivot table is located I'm using the code below thanks Sub ListPivotTablesNSource() Dim PT As PivotTable Dim Sh As Worksheet Dim lSh As Worksheet 'Loop Sheet Set Sh = Worksheets.Add Sh.Range("A1") = "Pivot Table Name" Sh.Range("B1") = "Pivot Table Source" Sh.Range("C1") = "Pivot Table Location" For Each lSh In ActiveWorkbook.Worksheets For Each PT In lSh.PivotTables With Sh.Range("A" & Sh.Rows.Count).End(xlUp) .Offset(1, 0) = PT.Name .Offset(1, 1) = PT.SourceData End With Next Next Columns("A:B").EntireColumn.AutoFit End Sub -- Helping Is always a good thing |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Pivot Table..getting a list of all, source data and sheets loc
Thanks...It's a little funny...I should not us "l" as like you I mistaked it
for i, which is why the code would not work... Thanks -- Helping Is always a good thing "Roger Govier" wrote: Sorry, I meant Ish.name -- Regards Roger Govier "QuietMan" wrote in message ... NBo...that would only get me the name of the sheet that i'm listing the pivot table information on...I need the name of the sheet where the pivot table located -- Helping Is always a good thing "Roger Govier" wrote:. Hi Isn't it just .Offset(1,2) = Sh.name -- Regards Roger Govier "QuietMan" wrote in message ... Can some one help me determine the sheet name that each pivot table is located I'm using the code below thanks Sub ListPivotTablesNSource() Dim PT As PivotTable Dim Sh As Worksheet Dim lSh As Worksheet 'Loop Sheet Set Sh = Worksheets.Add Sh.Range("A1") = "Pivot Table Name" Sh.Range("B1") = "Pivot Table Source" Sh.Range("C1") = "Pivot Table Location" For Each lSh In ActiveWorkbook.Worksheets For Each PT In lSh.PivotTables With Sh.Range("A" & Sh.Rows.Count).End(xlUp) .Offset(1, 0) = PT.Name .Offset(1, 1) = PT.SourceData End With Next Next Columns("A:B").EntireColumn.AutoFit End Sub -- Helping Is always a good thing |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot Table from multiple sheets? | Excel Discussion (Misc queries) | |||
| Pivot Table with Multiple sheets | Excel Worksheet Functions | |||
| suppress warning and locate cube in a pivot table from olap cube | Excel Discussion (Misc queries) | |||
| pivot table to sheets | Excel Worksheet Functions | |||
| multiple sheets into 1 pivot table | Excel Programming | |||
Linear Mode
