Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Double click pivot to get details using macro

  Asked By: Ashan    Date: Mar 17    Category: MS Office    Views: 1685
  

I have a pivot table with datas starting from the cell A5. A5 has the
name of a person while the rest of the cells have the informations.
Now, if I press cell D5, which is the last cell associated with the
name in A5, I get the details of all the datas of A5 in a new sheet.

Now, I want to make a macro which would allow me to click at D5, save
the report in a new workbook and save it as the name appearing in A5.
Next, it should click D6, again and save it as the name appearing in A6
and so on until all the cells with values are clicked (ie. range ("d5")

I would be very grateful if someone can throw a light on how to do
this.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Francis Riley     Answered On: Mar 17

Give this a try
Add this code to a module in your workbook  and then run it.

Change
YourExcelFileNameHere.xls to the name of you Excel File
and
ExcelTabWithPivotTable to the tab name thatg holds your pivot  table

Sub DoItAll()

Dim rng As Range, strTabName As String, intTabNum As Integer,
curLocation As String
strTabName = ""
intTabNum = 0
curLocation = ""
Range("D5").Select
Range(Selection, Selection.End(xlDown)).Select
For Each rng In Selection
If rng.Offset(0, -1) <> "Grand Total" Then
intTabNum = intTabNum + 1
curLocation = ActiveCell.Address
strTabName = ActiveCell.Offset(0, -3)
Selection.ShowDetail = True
Sheets("Sheet" & intTabNum).Select
Sheets("Sheet" & intTabNum).Name = strTabName
Worksheets(strTabName).Copy
Windows("YourExcelFileNameHere.xls").Activate
strTabName = ""
Sheets("ExcelTabWithPivotTable").Select
Range(curLocation).Offset(1, 0).Select
End If
'Sheets(strTabName).Move
Next rng

End Sub

 
Didn't find what you were looking for? Find more on Double click pivot to get details using macro Or get search suggestion and latest updates.




Tagged: