List the information of the first occurring Pivot Table from the Left or Column A or Row 1
Dear Forum,
I am using the below code to list the information of the Pivot Tables in a sheet, I just need some tweaking in the code so that I get the Pivot Table Information starting from the First Column A or First Row if there are Multiple Tables Side-By-Side or One-Above_Other....I am not exactly sure on what logic the information order is set..
Sub ListPivotsInfor()
'Update 20141112
Dim St As Worksheet
Dim NewSt As Worksheet
Dim pt As PivotTable
Dim I, K As Long
Application.ScreenUpdating = False
Set NewSt = Worksheets.Add
I = 1: K = 2
With NewSt
.Cells(I, 1) = "Name"
.Cells(I, 2) = "Source"
.Cells(I, 3) = "Refreshed by"
.Cells(I, 4) = "Refreshed"
.Cells(I, 5) = "Sheet"
.Cells(I, 6) = "Location"
For Each St In ActiveWorkbook.Worksheets
For Each pt In St.PivotTables
I = I + 1
.Cells(I, 1).Value = pt.Name
.Cells(I, 2).Value = pt.SourceData
.Cells(I, 3).Value = pt.RefreshName
.Cells(I, 4).Value = pt.RefreshDate
.Cells(I, 5).Value = St.Name
.Cells(I, 6).Value = pt.TableRange1.Address
Next
Next
.Activate
End With
Application.ScreenUpdating = True
End Sub
Now I have more than one Pivot Table in the Sheet but it shows the Information which is on the Right Hand Side first than the one on the Left Side, Likewise in another Sheet I have 2 Sheets one above each other so I want the first Table occurring from First Row or First Column.
Warm Regards
e4excel
Bookmarks