Macro para copiar datos de todos los excel de una carpeta

[amontoto]

Buenas,

Antes de nada, no tengo ni idea de programar macros. Las pocas que uso han sido copiadas googleando y personalizándolas para mis libros de forma básica.

Os planteo mi cuestión: necesito una macro que me copie en una sola hoja un rango de celdas (desde columnas B a P y filas desde 21 hasta la última cubierta) de todos los Excel de una carpeta (son todos iguales y parten de una plantilla, pero cada uno es cubierto por una persona distinta). Googleando y customizándola un poco he llegado a la siguiente:

Sub ImportDataAgent()

Dim SummarySheet As Worksheet

Dim FolderPath As String

Dim NRow As Long

Dim FileName As String

Dim WorkBk As Workbook

Dim SourceRange As Range

Dim DestRange As Range

' Create a new workbook and set a variable to the first sheet.

Set SummarySheet = ThisWorkbook.Sheets("hoja1")

' Modify this folder path to point to the files you want to use.

FolderPath = "\\Uvmcscbdat\2.CAJA 1045\CAJA 1045 AÑO 2015\11.NOVIEMBRE 2015\"

' NRow keeps track of where to insert new rows in the destination workbook.

NRow = 1

' Call Dir the first time, pointing it to all Excel files in the folder path.

FileName = Dir(FolderPath & "*.xl*")

' Loop until Dir returns an empty string.

Do While FileName <> ""

' Open a workbook in the folder

Set WorkBk = Workbooks.Open(FolderPath & FileName)

' Set the source range to be A9 through C9.

' Modify this range for your workbooks.

' It can span multiple rows.

Dim LastRow As Long

LastRow = WorkBk.Worksheets(12).Cells.Find(What:="*", _

After:=WorkBk.Worksheets(12).Cells.Range("A1"), _

SearchDirection:=xlPrevious, _

LookIn:=xlFormulas, _

SearchOrder:=xlByRows).Row

Set SourceRange = WorkBk.Worksheets(12).Range("B21:P" & LastRow)

' Set the destination range to start at column B and

' be the same size as the source range.

Set DestRange = SummarySheet.Range("A" & NRow)

Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _

SourceRange.Columns.Count)

' Copy over the values from the source to the destination.

DestRange.Value = SourceRange.Value

' Increase NRow so that we know where to copy data next.

NRow = NRow + DestRange.Rows.Count

' Close the source workbook without saving changes.

WorkBk.Close savechanges:=False

' Use Dir to get the next file name.

FileName = Dir()

Loop

' Call AutoFit on the destination sheet so that all

' data is readable.

SummarySheet.Columns.AutoFit

End Sub

Básicamente me funciona, pero me da ciertos problemillas sobre los que solicito ayuda:

1. En los libros que no contienen datos me copia el encabezado y una fila en blanco (B:20 a P:20) que no me interesa (entiendo que es porque al estar sin datos es la que localiza como LastRow. Si alguien me puede decir como hacer para que no copie nada si el libro no tiene nada de la fila 20 para abajo.

2. Los Excel donde están los datos de origen tienen una hoja para cada día del mes. Lo soluciono entrando cada día en la macro y cambiando la hoja de origen con el número que de hoja que me interesa. ¿habría alguna forma de que ese dato lo cogiera de una celda del Excel donde los pudiera modificar sin tener que entrar en la macro?

3. Relacionado con lo anterior, la carpeta donde están los Excel de origen cambia también cada mes. ¿Podría coger la ruta de una celda del Excel de destino para cambiarla desde allí sin tener que entrar y modificarlo en la macro?

Muchas gracias por adelantado por la ayuda. La verdad es que llevo bastante tiempo intentando encontrar solución a estos problemillas y no encuentro manera, mis conocimientos de macros son limitados, lo poco que sé es a base de copiar macros ya hechas e ir modificando a base de prueba-error. Igual esta macro que pongo es un patochada y hay una forma mucho más fácil de hacerlo, pero con mis conocimientos es hasta donde he podido llegar.

Un saludo