Bom dia,
Criei uma macro para gerar uma tabela dinâmica, só que ao executar gera erro em tempo de execução "5", segue minha macro.:
Sub Macro6()
[b][color=#FF0000] ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"RESUMO DE VENDAS!R1C1:R1048576C10", Version:=xlPivotTableVersion15). _
CreatePivotTable TableDestination:="DINAMICA VENDAS!R1C1", TableName:= _
[/color][/b] "Tabela dinâmica4", DefaultVersion:=xlPivotTableVersion15
Sheets("DINAMICA VENDAS").Select
Cells(1, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("Tabela dinâmica4").PivotFields( _
"Nota Fiscal/Serie")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Tabela dinâmica4").PivotFields(" Vlr.Total")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("Tabela dinâmica4").PivotFields(" Vlr.IPI")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("Tabela dinâmica4").PivotFields("ST")
.Orientation = xlRowField
.Position = 4
End With
ActiveSheet.PivotTables("Tabela dinâmica4").AddDataField ActiveSheet. _
PivotTables("Tabela dinâmica4").PivotFields(" Vlr.Total"), _
"Contagem de Vlr.Total", xlCount
ActiveSheet.PivotTables("Tabela dinâmica4").AddDataField ActiveSheet. _
PivotTables("Tabela dinâmica4").PivotFields(" Vlr.IPI"), "Contagem de Vlr.IPI" _
, xlCount
ActiveSheet.PivotTables("Tabela dinâmica4").AddDataField ActiveSheet. _
PivotTables("Tabela dinâmica4").PivotFields("ST"), "Contagem de ST", xlCount
Range("B1").Select
With ActiveSheet.PivotTables("Tabela dinâmica4").PivotFields( _
"Contagem de Vlr.Total")
.Caption = "Soma de Vlr.Total"
.Function = xlSum
End With
Range("B1").Select
With ActiveSheet.PivotTables("Tabela dinâmica4").PivotFields( _
"Soma de Vlr.Total")
.Caption = "valor total da vendas"
.NumberFormat = "#.##0,00_);[Vermelho](#.##0,00)"
End With
Range("C1").Select
With ActiveSheet.PivotTables("Tabela dinâmica4").PivotFields( _
"Contagem de Vlr.IPI")
.Caption = "valor do ipi"
.Function = xlSum
.NumberFormat = "#.##0,00_);[Vermelho](#.##0,00)"
End With
Range("D1").Select
With ActiveSheet.PivotTables("Tabela dinâmica4").PivotFields("Contagem de ST")
.Caption = "valor do ST"
.Function = xlSum
.NumberFormat = "#.##0,00_);[Vermelho](#.##0,00)"
End With
ActiveSheet.PivotTables("Tabela dinâmica4").CalculatedFields.Add "Campo1", _
"=' Vlr.Total' +' Vlr.IPI' +ST", True
ActiveSheet.PivotTables("Tabela dinâmica4").PivotFields("Campo1").Orientation _
= xlDataField
Range("E1").Select
ActiveSheet.PivotTables("Tabela dinâmica4").PivotFields("Soma de Campo1"). _
Caption = "valor total"
Range("G1").Select
End Sub
Postado : 14/06/2018 9:24 am