Hola a todos!!!!
Necesito una pequeña ayuda con la programación de macros en Excel
Obtengo un error '5' cuando quiero crear una segunda PivotTables a partir de una misma PivotCache!!!!!, ke estare haciendo mal?
, este es mi código:
''''''''''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("O22").Select
Selection.Copy
Range("O2" & 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
Todo va bien cuando hago la primer PivotTable....
Pero cuando quiero crear una segunda PivotTable obtengo el error '5' !!!!!!! Que mala suerte, ¿Qué podra ser?
Public Sub.... bla bla....
Set PT = PTC.CreatePivotTable(TableDestination:="'[" & sFileName + "]" & PivotTable_DestinationSheet & "'!" & PivotTable_Cell, TableName:="" & PivotTable_Name)
Set PT = Nothing
bla bla ... End Sub
Ayudenme porfa, soy solo una principiante
