Hello there!!!! (Sorry about my bad english, it's not my native language)
I need a little help for a Pivot Table's programming problem...
i got a "Runtime Error '5'" when i want to create a second PivotTabel from a PivotCache Object (the same)... My code is this:
''''''''''Starting'''''''''''''''''''''
sFileName = ActiveWorkbook.Name
MsgBox "Por favor ubique el reporte ""Consolidado"" de SAP"
sfileToOpen = Application.GetOpenFilename("Archivos de Excel (*.xls), *.xls")
nAux = InStrRev(sfileToOpen, "\") + 1
sArchivo = Mid(sfileToOpen, nAux, Len(sfileToOpen) - nAux + 1)
sPath = Mid(sfileToOpen, 1, nAux - 1)
If sfileToOpen = "Falso" Then Exit Sub
Sheets("BD").Select
Range("A1:N1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Workbooks.Open Filename:=sfileToOpen
'Sheets("Hoja1").Select
Range("A1:N1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("" & sFileName).Activate
Sheets("BD").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
Application.DisplayAlerts = True
Workbooks("" & sArchivo).Close SaveChanges:=False
Windows("" & sFileName).Activate
'''''''''''''''''''''''''''''''''''''''
''''''''''''Setting up Pivot Cache'''''''''''''''''
Set PTDB = Worksheets("BD")
FinalRow = PTDB.Cells(Application.Rows.Count, 1).End(xlUp).Row
FinalCol = PTDB.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Set PTCR = PTDB.Cells(1, 1).Resize(FinalRow, FinalCol)
'MsgBox ActiveWorkbook.PivotCaches.Count
Set PTC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PTCR.Address)
'MsgBox ActiveWorkbook.PivotCaches.Count
Sheets("TD").Select
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''...Setting up Pivot Cache'''''''''''
Sheets("TD").Select
Range("O2:S2").Select
Selection.Copy
Range("O2:S" & FinalRow).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
Sheets("Graficos").Select
sEmpresa = Range("E6").Value
sPstoTrab = Range("I6").Value
Range("A1").Select
Sheets("TD").Select
For Each PT In ActiveSheet.PivotTables
PT.TableRange2.Clear
Next
''''''''''''''OK, I NEED TO CREATE n PIVOT TABLES IN A SPECIAL SUB....
Public Sub.... bla bla....
Set PT = PTC.CreatePivotTable(TableDestination:="'[" & sFileName + "]" & PivotTable_DestinationSheet & "'!" & PivotTable_Cell, TableName:="" & PivotTable_Name)
Set PT = Nothing
bla bla ... End Sub
Everything goes well the first time that i make a PivotTable...
But when i want to create a second PivotTable from the same PivotCache i got an error '5'...
Public Sub.... bla bla....
Set PT = PTC.CreatePivotTable(TableDestination:="'[" & sFileName + "]" & PivotTable_DestinationSheet & "'!" & PivotTable_Cell, TableName:="" & PivotTable_Name)
Set PT = Nothing
bla bla ... End Sub
Please help meee, i'm just a Begginerrrrrrrrrrrrr
Bookmarks