Extracción de datos de varias hojas

Debieras copiar la macro que mencionas, para saber de qué se trata.
Para utilizar una rutina en hojas diferentes, utilizá ActiveSheet en lugar del nombre como Sheets("Hoja1")
Para tomar todas las hojas necesitas un bucle de este tipo:
For each sh in Sheets
Y luego ttrabajar con sh, por ej: sh. Copy
Si esto no ayuda, escribí aquí tu macro para que pueda ajustarla.

Hola gracias, por el aporte, lo que pasa es que casi no se nada de macros, la macro que había encontrado aquí es esta junto con la explicación que se hace abajo: De hecho la hice y funciono siempre y cuando las hojas de cada uno de los libros sea la misma, pero tengo problemas cuando se trata de hojas con nombre diferente. Es por eso que necesito, que si es posible se modifique la macro para poder copiar de cada libro los rangos que quisiera establecer sin importar el nombre de las hojas.
La explicación comienza así...
Tal como lo pediste, aquí tienes una macro que desarrollé para que ejecute una determinada acción sobre varios libros.
Para su funcionamiento solo necesitas crear un archivo nuevo que será un auxiliar para juntar todos los archivos en un único archivo.
Este archivo debe tener una hoja llamada INICIO.
En ella, coloca en la celda C7 la dirección de la carpeta donde debe buscar todos los archivos.
En la celda C8, ingresa qué tipo de archivos debe considerar. Esto te permite, seleccionar algunos de los archivos del directorio indicado en C7. Así, por ejemplo, si quieres que sólo considere los archivos que empiezan con "Fact", coloca en esa celda:
Fact*.xls
Si quieres que traiga todos los archivos, simplemente deja en tal celda:
*.xls
La celda C9 permite que coloques una clave, en caso de que los archivos a abrir tuvieran la misma clave de protección para apertura.
Si no tuviesen clave, deja esa celda en blanco.
Como no dijiste, específicamente de qué hoja debe tomar el rango B17:F52, la celda C10 recibirá tal dato.
Finalmente, la celda C11 tendrá el rango a traer (en tu caso:B17:F52)
Otra hoja, que llamaré "DATOS", recibirá desde la celda B4 los rangos de tus archivos
Graba este archivo.
Ahora falta colocar la macro que hace el trabajo.
Activa el editor de Visual Basic (presiona Alt+F11), inserta un nuevo módulo ("Insertar", "´Modulo") y pega el siguiente código:
Sub Pegarango()
'==== Variables del sistema
Dim MiCarpeta As String, MisArchivos As String, MiClave As String
'manuales:
Hoja_dest = "DATOS"
Celda_ini = "B4"
'tomadas de Hoja Inicio:
MiCarpeta = Trim(Sheets("INICIO").Range("C7").Value) & IIf(Right(Trim(Sheets("INICIO").Range("C7").Value), 1) = "\", "", "\")
MisArchivos = Trim(Sheets("INICIO").Range("C8").Value)
MiClave = Trim(Sheets("INICIO").Range("C9").Value)
DeHoja = Trim(Sheets("INICIO").Range("C10").Value)
ElRango = Trim(Sheets("INICIO").Range("C11").Value)
With Application.FileSearch
.LookIn = MiCarpeta
.SearchSubFolders = True
.FileName = MisArchivos
If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then
Sheets(Hoja_dest).Select
Range(Celda_ini).Select
For i = 1 To .FoundFiles.Count
DirFile = .FoundFiles(i)
Workbooks.Open FileName:=DirFile, Updatelinks:=False, PASSWORD:=""
Application.Calculation = xlManual
ActiveWorkbook.Unprotect PASSWORD:=MiClave
'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'
'* TAREA A EFECTUAR en archivo: PEGAR SECUENCIALMENTE RANGO DE CADA ARCHIVO
'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'
'Application.ScreenUpdating = False
Application.CutCopyMode = False
Sheets(DeHoja).Select
Range(ElRango).Copy
ActiveWindow.ActivatePrevious
Sheets(Hoja_dest).Select
With Selection
.PasteSpecial Paste:=xlValues
.PasteSpecial Paste:=xlFormats
End With
'ActiveSheet.Paste '<- Activa este si quieres que
'pegue los datos copiados tal como están en archivo
'de origen (y desactiva las cuatro lineas anteriores)
ActiveCell.Offset(Selection.Rows.Count + 1).Select
Application.CutCopyMode = False
ActiveWindow.ActivateNext
ActiveWorkbook.Close False
Sheets("INICIO").Select
Application.ScreenUpdating = True
'=============================================================
' FIN tarea en archivo
'=============================================================
Next i
MsgBox i - 1 & " ARCHIVOS AGREGADOS"
Else
MsgBox "No se encontró ningún archivo " & MisArchivos & " en " & Chr(10) & MiCarpeta
End If
End With
End Sub
Notarás que la mayoría de los datos están parametrizados, es decir que podrás cambiar la información en las variables sin tener que modificar el procedimiento. Un par se cargan dentro del código y el reto las toma de la hoja INICIO
Cierra el editor de visual basic y graba el archivo.
Hacia la mitad de la macro notarás que está indicada cuál es la tarea que debe realizar en cada archivo.
De esta manera, si otro día quieres hacer otra cosa con varios archivos en un directorio, bastará que coloques el código a ejecutar en este lugar.
El toque final sería colocar un botón en la hoja INICIO para activar la macro.
Para esto, una vez copiada la macro en el archivo, muestra la barra de herramientas de "Formularios" ("Ver" | "Barras de herramientas" | "Formularios".
De ella, toma el icono de "botón" y dibújalo en tu hoja. Al haber presionado el icono, el cursor del mouse se transformará en una cruz fina. Mantén presionado el botón derecho y arrástralo hasta donde desees dibujando el botón.
Cuando sueltes la tecla, automáticamente aparecerá la lista de macros disponibles de dónde podrás seleccionar el que quieres asociar. Simplemente, dale doble click.
También, puedes cambiar la vinculación de un botón con una macro, dando click derecho sobre él y eligiendo "Asignar macro". Otra vez, la lista de macros disponibles te permitirá realizar tal asignación.
Listo. Tienes un archivo que abrirá uno de los que te mandaron. Copiará el rango indicado, uno a continuación de otro con una línea de separación y luego cerrará ese archivo. Posteriormente, hará lo propio con el siguiente y así, hasta terminar con todos los indicados.
Finalmente bastará conque grabes este archivo combinado, dándole otro nombre por supuesto, y luego puedes eliminar la hoja INICIO y el módulo de la macro si no las necesitas.
Esto debería resolver tu pregunta. Si así fuera, agradeceré un comentario y que la finalices.
PD: Si existiera algún otro macro, le agradecería muchísimo que me lo brindaras, ya que tengo muchos libros y necesito unirlos todos en uno solo, los mismos tienen hojas de diferentes nombres,