(Solved) – Fill blanks with zeros in Excel VBA. On a newly created dynamic table

(solved)-–-fill-blanks-with-zeros-in-excel-vba.-on-a-newly-created-dynamic-table

I am new using VBA. I just created a macro which I want the blanks to fill with zero automatically, in that same macro.
I have tried several ways but it marks me error.
Tried to use the following line of code:

ActiveSheet.PivotTables("TablaDepartamentos").NullString = "0"

I use this line of code since the pivot table is renamed:

'Rename pivot table 
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
For Each pt In ws.PivotTables
    pt.Name = ws.Name

Next pt
Next ws
End With

Next, I leave the code for you to see what I am doing. Many thanks:

Sub PRUEBA()
Dim pt As PivotTable
Dim PTCache As PivotCache

'Revisa que no existan hojas con ese nombre y si lo hay, lo alimina
On Error Resume Next
Application.DisplayAlerts = False
Sheets("TablaDepartamentos").Delete
On Error GoTo 0

'Obtener la informacion de la tabla
Set PTCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=Sheets("ECC-EWM").Range("A1").CurrentRegion.Address)


'Crear una nueva worksheet
Worksheets.Add
ActiveSheet.Name = "TablaDepartamentos"

'Crear la tabla pivote
Set pt = ActiveSheet.PivotTables.Add( _
    PivotCache:=PTCache, _
    TableDestination:=Range("A1"))

'Crear columna de la tabla
 With pt.PivotFields("System")
    .Orientation = xlColumnField
    .Position = 1
End With

'Crear fila de la tabla
With pt.PivotFields("Depart")
 .Orientation = xlRowField
 .Position = 1
End With

'Table information
With pt.PivotFields("Count")
 .Orientation = xlDataField
 .Position = 1
 .Function = xlSum
 .NumberFormat = "#,##0"

'Rename pivot table 
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
For Each pt In ws.PivotTables
    pt.Name = ws.Name

Next pt
Next ws
End With
End Sub

Thank you very much

Leave a Reply

Your email address will not be published. Required fields are marked *