VB Help If Statement in Selection.ShowDetail = True

[TroyT]

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

 

[Joel]

try something like this

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\" & i & ".csv", _
FileFormat:=xlCSV, _
CreateBackup:=False
BookCount = BookCount + 1
100 ColCount = ColCount + 3
Next sht
End Sub

 

[Joel]

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]

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..

 

[TroyT]

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

 

[Joel]

You were getting an error because the selected cell was not inside a pivot
table. I'm not sure if this is the simpliest method to determine if a cell
is inside a pivot table. but it seem to prevent errors. Not sure if it does
what you want. I concerned wit your original code. Are you using a Pivot
table chart?


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

ColCount = 3
BookCount = 1
ChDir "C:\temp\test"

'check each worksheet
For Each sht In ThisWorkbook.Sheets
'check if pivot table is in range of selected cell
Found = False
'look at each pivot table on worksheet
For Each piv In sht.PivotTables
'covert address of pivot table from R1C1 to A1
Pivaddr = Application.ConvertFormula( _
Formula:=piv.SourceData, _
fromReferenceStyle:=xlR1C1, _
toReferenceStyle:=xlA1)
'remove sheet name from pivot table address
Pivaddr = Mid(Pivaddr, InStr(Pivaddr, "!") + 1)
'check if intersection of pivot table and cell address match
Set IsPivotTable = Application.Intersect(sht.Range(Pivaddr), _
sht.Cells(7, ColCount))

'exit loop if cell address is inside a pivot table
If Not IsPivotTable Is Nothing Then
Found = True
Exit For
End If
Next piv

'if cell address is inside a pivot table
If Found = True Then

'show details
sht.Cells(7, ColCount).ShowDetail = True
'save workbook
ActiveWorkbook.SaveAs Filename:="C:\test\" & BookCount & ".csv", _
FileFormat:=xlCSV, _
CreateBackup:=False
BookCount = BookCount + 1
ColCount = ColCount + 3
End If
Next sht
End Sub