Ayudas con macros en excel.

buenas noches amigos de todoexpertos, hice una macro en que me ordena y me modifica unos registro en un libro de excel, que tiene de error es que cuando ba a crear una tabla dinámica me manda un error y me muestra donde esta el error del código como esta en esta imagen http://flic.kr/p/cRjAdU pues la verdad no se que paso con eso, la otra cosa que me gustaría hacerle a la macro es que hay 4 columnas que tienen celdas en blanco o vacías y las tengo que filtrar que me selecciones esas celdas para poderlas llenar, con un cero " 0 "

pero la cuando ise la macro solo lleno dos celdas que estaban vacías, y hoy me di cuenta que habían mas de 2 celdas que estaban vacías la pregunta es puedo crear algún tipo de función que me recora las celdas que están vacías y las llene con cero "0" este es el código de la macro

Sub GESTION()
'
' GESTION Macro
'
'
Rows("1:5").Select
Range("H5").Activate
Selection.Delete Shift:=xlUp
Columns("H:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H1").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[1],"" "",RC[2],"" "",RC[3],"" "",RC[4],"" "",RC[5])"
Selection.AutoFill Destination:=Range("H1:H5000")
Range("H1:H5000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("formato-005_ENTREGA").Select
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hoja1").Select
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("formato-005_ENTREGA").Select
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hoja1").Select
Columns("C:C").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("formato-005_ENTREGA").Select
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hoja1").Select
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("formato-005_ENTREGA").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
Columns("O:O").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hoja1").Select
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("formato-005_ENTREGA").Select
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
Columns("T:V").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hoja1").Select
Columns("F:F").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("formato-005_ENTREGA").Select
Columns("W:W").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hoja1").Select
Columns("I:I").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("formato-005_ENTREGA").Select
Columns("Z:Z").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hoja1").Select
Columns("J:J").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("formato-005_ENTREGA").Select
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
Columns("AC:AC").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hoja1").Select
Columns("K:K").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.RowHeight = 26.25
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A1").Select
ActiveCell.FormulaR1C1 = "NIC"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("B1").Select
ActiveCell.FormulaR1C1 = "MUNICIPIO"
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("C1").Select
ActiveCell.FormulaR1C1 = "BARRIO"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline =...

Añade tu respuesta

Haz clic para o