Solved

vba help

Posted on 2014-11-14
2
91 Views
Last Modified: 2014-11-14
Hi

i am looking for a vba code that when i run in a workbook with so many pivot tables, in a new sheet it would create a report like below

Pivot Table Name   like this  Pivottable1
Pivot Table data source SheetName  like Sheet1 etc
Pivot table Data source range address like A1:D400

in addition if it also can put the pivot table data source background color

thanks,.
0
Comment
Question by:Flora
2 Comments
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40443291
here it is

Sub listpp()

 Dim pvt As PivotTable
 Dim iSht As Long
 Dim iRow As Integer

 Application.ScreenUpdating = False
 Set objNewSheet = Worksheets.Add
 objNewSheet.Activate

 iRow = 2
 iSht = 2

 'SET TITLES
 Range("A1").FormulaR1C1 = "Name"
 Range("B1").FormulaR1C1 = "Source"
 Range("C1").FormulaR1C1 = "Refreshed by"
 Range("D1").FormulaR1C1 = "Refreshed"
 Range("E1").FormulaR1C1 = "Sheet"
 Range("F1").FormulaR1C1 = "Location"

 'GET PIVOT DETAILS
 Do While iSht <= Worksheets.Count
 Sheets(iSht).Select

 For Each pvt In ActiveSheet.PivotTables
 objNewSheet.Cells(iRow, 1).Value = pvt.Name
 objNewSheet.Cells(iRow, 2).Value = pvt.SourceData
 objNewSheet.Cells(iRow, 3).Value = pvt.RefreshName
 objNewSheet.Cells(iRow, 4).Value = pvt.RefreshDate
 objNewSheet.Cells(iRow, 5).Value = ActiveSheet.Name
 objNewSheet.Cells(iRow, 6).Value = pvt.TableRange1.Address
 iRow = iRow + 1
 Next

 iSht = iSht + 1
 Loop

 objNewSheet.Activate

 Application.ScreenUpdating = True

 End Sub

Open in new window

0
 
LVL 6

Author Closing Comment

by:Flora
ID: 40443308
thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question