Creación de reporte con macros en excel

Reporte automatizado en excel

Muy buenas tardes tengan todos, señores en la empresa donde laboro todos los días actualizo un informe donde gasto varias horas de mi tiempo haciendo pasos repetitivos, lo que quisiera es aprende a realizar una macro que automatice mi trabajo diario

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
'Cambiar la ruta de la carpeta de archivos excel aquí
Set dirObj = mergeObj.Getfolder("C:\Tecnosoft\Excel\Lotes")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
'Cambie "A2" con referencia de celda de punto de inicio para cada archivo aquí
'Por ejemplo "B3: IV" para combinar todos los archivos comienzan de columnas B y filas 3
'Si se trata de archivos que utilizan más de la columna IV, cambie a la última columna
'También cambie la columna "A" en "A65536" a la misma columna que el punto de inicio
Range("A1:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(2).Activate
'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
On Error Resume Next
Next
End Sub

con el siguiente código lo que hago es unificar todos los libros en excel y realizar un solo informe.

El tema es que en el informe quisiera que realizara lo siguiente:

1. Borrar todos las celdas vacías

2. Que inserte una columna con la palabra lotes

3. Que colocara el lote pertenecientes a todas las orden ( el mismo lote a cada orden)

Les agradecería mucho si me pueden guiar.