Also when i add it to the file with the Pivot tables it gives me an error at
the same location..
Unable to set the ShowDetal property of the Range Class. RunTime Error 1004

"TroyT" wrote:

It fails on
sht.Cells(7, ColCount).ShowDetail = True

Im trying to run this in Excel 2007, and I am saving this macro in a macro
enabled workbook, which is always open. Then opening the file i need to get
the data from the pivot table. Is this the best way to do it? I can kinda
follow what you are doing, but i am still lost..

Thanks for your help..

"Joel" wrote:

I had i instead of bookcount

Sub getdata()
'
' getdata Macro
'
' Keyboard Shortcut: Ctrl+q
'

ColCount = 3
BookCount = 1
ChDir "C:\test"
For Each sht In ThisWorkbook.Sheets
On Error GoTo 100
sht.Cells(7, ColCount).ShowDetail = True
ActiveWorkbook.SaveAs Filename:="C:\test\" & BookCount & ".csv", _
FileFormat:=xlCSV, _
CreateBackup:=False
BookCount = BookCount + 1
100 ColCount = ColCount + 3
Next sht
End Sub


"TroyT" wrote:

Hello,
I have recorded almost what I need and hoped you guys here would be able to
help me. This Macro will
Drill down into 8 specifically placed pivot tables on a single worksheet,
and save the individual files to c:\test.csv

The macro works if every pivot table has data, but if its blank it fails.
Also, i need help in making the file names change. It could be previous +1
or anything as long as there is never a duplicate..
Here is the macro.

Sub getdata()
'
' getdata Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("C7").Select
Selection.ShowDetail = True
ChDir "C:\test"
ActiveWorkbook.SaveAs Filename:="C:\test\1.csv", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveSheet.Next.Select
Range("F7").Select
Selection.ShowDetail = True
ActiveWorkbook.SaveAs Filename:="C:\test\2.csv", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveSheet.Next.Select
Range("I7").Select
Selection.ShowDetail = True
ActiveWorkbook.SaveAs Filename:="C:\test\3.csv", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveSheet.Next.Select
Range("L7").Select
Selection.ShowDetail = True
ActiveWorkbook.SaveAs Filename:="C:\test\4.csv", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveSheet.Next.Select
Range("O7").Select
Selection.ShowDetail = True
ActiveWorkbook.SaveAs Filename:="C:\test\5.csv", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveSheet.Next.Select
Range("R7").Select
Selection.ShowDetail = True
ActiveWorkbook.SaveAs Filename:="C:\test\6.csv", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveSheet.Next.Select
Range("U7").Select
Selection.ShowDetail = True
ActiveWorkbook.SaveAs Filename:="C:\test\7.csv", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveSheet.Next.Select
Range("X7").Select
Selection.ShowDetail = True
ActiveWorkbook.SaveAs Filename:="C:\test\8.csv", FileFormat:=xlCSV, _
CreateBackup:=False
ActiveSheet.Next.Select
ActiveWindow.Close
End Sub