VBA code to copy to worksheet from pivot table

[danboi10]

Hi,

Have used the access forum but this is my first foray into excel! I am hoping that someone maybe able to assist with the following query:

I have a pivot table that has two columns and 29 rows. The essential function that i'd like the VBA code to perform is to open each row to a new worksheet and the for each (29) worksheets to be copied and transferred into new workbooks (the workbooks would then be saved a name contained in the cell). I have been able to write a code that performs this function but i cannot get it down the pivot table repeating this. Code attached below:

Sub Macro1()
'
' Macro1 Macro
Dim Cell As Range
Dim b As Integer
Dim a As String
Dim d As String
Dim ws As Worksheet


Range("B4").Select
Selection.ShowDetail = True
ActiveSheet.Name = Range("D2").Text
Cells.Select
Selection.Copy
ActiveSheet.Name = Range("D2").Text
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
ActiveSheet.Name = Range("D2").Text
Cells.Select
    Cells.EntireColumn.AutoFit
    ActiveWindow.Zoom = 85
    Selection.RowHeight = 14.25
    Cells.EntireColumn.AutoFit
Sheets("Sheet2").Select
    ActiveWindow.SelectedSheets.Delete
Sheets("Sheet3").Select
    ActiveWindow.SelectedSheets.Delete
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Columns("F:F").Select
Selection.Delete Shift:=xlToLeft
ActiveWorkbook.SaveAs Filename:="J:\" & Range("D2")
ActiveWorkbook.Close
End Sub

 

[Trevor G]

Look at recording a Macro to go into the Pivot and where you have the very last cell the grand total double click it, it will then give you a separate sheet with the data on it. Example shown below. You can then use your code to go through each row.

Range("L26").Select
Selection.ShowDetail = True

 

[danboi10]

Thanks for the reply Trevor. Unfort i do need it to open from the pivot table. However, you did give me the idea to go right back to basics!!!!

Dan