Para empezar la Automatización de Reportes con Macros en Excel, nos ayudara a hacer el trabajo de día a horas o incluso minutos, con ayuda de Visual Basic.

En este caso retomaremos el ejercicio anterior, pero en esta ocasión lo resolveremos con una MACRO

Una vez tengamos abierto nuestro archivo presionamos alt+F11 para acceder al editor de visual basic y nos aparecerá la Macro después presionamos alt+I+M para generar un nuevo módulo.

  1. Una ves este nuestro modulo activo pondremos Sub Master () este es el nombre de nuestra MACRO
  2. Dim fila As Double nos permite crear nuestro rango dinámico
  3. Dim rgfiltro As Range esta es una variable de tipo “RAGE”
  4. Sheets(“BD”).Select nos permite seleccionar nuestra hoja de BD desde aquí podremos modificar nuestra hoja.
  5. Presionamos Ctrl + barra espaciadora fila = Range("C1").End(xlDown).Row esto nos dará el control sobre todas las filas, ya si agregamos o quitamos filas nuestro rango lo reconocerá.
  6. Print fila
  7. Range("D1:F1").EntireColumn.Insert shift:=xlRight con esto agregamos 3 comlunmas hacia la derecha a partir de la columna D.
  8. Range("C1").EntireColumn.TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, fieldInfo:=Array(Array(0, 1), Array(10, 1), Array(12,1)), TrailingMinusNumbers:=True :el nombre de todos estos argumentos nos permitirá separar el texto en columnas.

Depuración de la Base de Datos

Después de esto empezaremos depurar nuestra BD de la siguiente forma:

  1. Range("D1").EntireColumn.Delete Shift:=xlRight
  2. Range("D1").Value = "Descripción"
  3. Range("E1").Value = "Categoría"
  4. Range("E3").FormulaR1C1 = "=MID(RC(-1),1,SEARCH("" ""),RC(-1,2))"
  5. Range("E3").FormulaR1C1 = "=MID(RC(-1),1,SEARCH("" ""),RC(-1,2))"
  6. Range("A3").FormulaR1C1 = "=R(-1)C"
  7. Range("B3").FormulaR1C1 = "=R(-1)C"
  8. Set rgfiltro = Worksheets("BD").Range("A1:G" & fila)

With rgfiltro

Empezamos con los filtros y  lo aremos columna por columna, con “Autofilter”  nos ayudara a filtrar todas las celdas vacías celdas visibles

  1. .AutoFilter
  2. .AutoFilter field:=4, Criterial:="<>"
  3. Range("E3:E" & fila).SpecialCells(xlCellTypeVisible).FormulaR1C1 = Range("E3").FormulaR1C1
  4. .AutoFilter
  5. .AutoFilter field:=1, Criterial:=""
  6. Range("A3:A" & fila).SpecialCells(xlCellTypeVisible).FormulaR1C1 = Range("A3").FormulaR1C1
  7. .AutoFilter
  8. .AutoFilter field:=2, Criterial:=""
  9. Range("B3:B" & fila).SpecialCells(xlCellTypeVisible).FormulaR1C1 = Range("B3").FormulaR1C1
  10. .AutoFilter

Estas fórmulas se aplican (Autofilter) ya que son rangos dinámicos y así se repite el siclo para el auto filtro

Para que nuestra macro no marque ningún error copiaremos toda nuestra BD y la pegaremos como valores así al filtrar nuestra información nos marcara ningún error.

Finalmente vamos a re-definir nuestra variable de filas ya que nuestros filtros anularon las filas vacías de este modo tendremos depurada nuestra BD.

En este punto de la macro insertaremos la Tabla Dinámica en la cual se verán reflejados los valores de nuestra BD depurada.

  1. ActiveSheets.Name="TD"
  2. Set rgfiltro = Worksheets("BD"). Range("A1:G" & fila)
  3. Private rgTD As String
  4. Private rgCoincidir As String
  1. Sub Crear_TD(rgfiltro As Range)
  2. CutCopyMode = False
  3. Add
  4. Name = "TD"
  5. PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
  6. rgfiltro, Version:=6).CreatePivotTable TableDestination:= _
  7. Worksheets("TD").Range("A3"), TableName:="TD", DefaultVersion:=6
  8. Sheets("TD").Select
  9. Cells(3, 1).Select
  10. With ActiveSheet.PivotTables("td")

Ya por último llamamos nuestra TD para que se ejecute después de depurar la BD.

Llenado del Reporte

Ya en este punto vamos a insertar las fórmulas de BUSCARV, vamos a efectuar las sumas.

Sub INSERTAR_FORMULAS()

  1. Dim rgTD As String
  2. Dim rgCoincidir As String
  3. rgTD = "TD!R4C1:R46C12"
  4. rgCoincidir = "TD!R4C1:R4C12"
  5. Sheets("Report").Select
  6. Range("C4").Value = "Bieldo"
  7. Range("D4").Value = "Bieldo Jardin"
  8. Range("E4").Value = "Candado"
  9. Range("F4").Value = "desarmador"
  10. Range("G4").Value = "Linterna"
  11. Range("H4").Value = "Cincel"
  12. Range("I4").Value = "Cinta"
  13. Range("J4").Value = "Antena"
  14. Range("K4").Value = "Foco"
  15. Range("C4").Value = "Bieldo"
  16. Range("C5:K5, C10:K16, C12:K23, C28:K29, C34:K41, C46:K53, C58:K60, C65:K72").FormulaR1C1 = "=VLOOKUP(""*""&RC1&""*""," & rgTD & "MATCH(""*""&R4C&""*""," & rgCoincidir & ",0),0)"
  17. Range("C6:K6").FormulaR1C1 = "=R(-1)C"
  18. Range("C17:K17").FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
  19. Range("C24:K24").FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
  20. Range("C30:K30").FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
  21. Range("C42:K42").FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
  22. Range("C54:K54").FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
  23. Range("C61:K61").FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
  24. Range("C73:K73").FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
  25. Range("C75:K75").FormulaR1C1 = "=VLOOKUP(""*""&RC26&""*""," & rgTD & "MATCH(""*""&R4C&""*""," & rgCoincidir & ",0),0)"
  26. Range("C77:K77").FormulaR1C1 = "=R6C+R17C+R24C+R30C+R42C+R54C+R61C+R73C"

End Sub

Hasta este punto hemos hecho todas las sumas de nuestro reporte; de esta manera, el llenado de este se realza de forma automática ya que nuestra macro nos permite realizar lo de una forma más optima y eficaz.

Como siguiente paso agregaremos al ultimo vendedor y así nuestra comprobación quedara sin errores y nuestro reporte quedara lleno sin ninguna diferencia.

Para este paso agregaremos otra macro la cual nos ayudará para agregar un nuevo apartado el cual contendrá los datos del vendedor que falta para cuadrar nuestro reporte.

Sub INSERTAR_VENDEDOR()

  1. Range("A4:A7").EntireRow.Insert shift:=x1up
  2. Range("A8:A10").Copy
  3. Range("A4").PasteSpecial (xlPasteAll)
  4. Range("A5").Value = "111"
  5. Range("B5").Value = "HUMBERTO RAMIREZ JUAREZ"
  6. Range("B4").Value = "CIUDAD DE MEXICO"
  7. Range("C81:K81").FormulaR1C1 = Range("C81").FormulaR1C1 & "+R6C"
  8. Range("C83:K83").FormulaR1C1 = "=R81C=R79C"

Y finalmente correremos nuestro maco agregando nuestras variables para que el ejercicio se ejecute de manera correcta y completa.

Personalizar Cinta de Opciones

Por ultimo y no menos importante agregaremos nuestro botón de ejecución para la macro el cual nos permitirá correr nuestra macro desde el principio y cada vez que lo necesitemos.

Nos dirigimos a nuestra cinta de opciones para poder agregar nuestro boton de la macro.

Automatización de Reportes con Macros

Deja una respuesta