Copiar un rango establecido de varias hojas de trabajo a una nueva hoja de trabajo (Microsoft Excel)
Om tiene cientos de hojas de trabajo con nombres diferentes en el mismo libro de trabajo. La estructura de cada hoja de trabajo es esencialmente la misma. Quiere copiar un rango fijo (A146: O146) de cada hoja de trabajo a una nueva hoja de trabajo, una fila tras otra.
Hay varias formas de hacerlo. Por mi dinero, una simple macro es la mejor opción. Sin embargo, antes de entrar en el enfoque basado en macros, hay una manera de hacerlo mediante fórmulas. Una forma bastante única se basa en definir una fórmula en el Administrador de nombres. Siga estos pasos para comenzar:
-
Muestre la pestaña Fórmulas de la cinta.
-
En el grupo Nombres definidos, haga clic en la herramienta Administrador de nombres. Excel muestra el cuadro de diálogo Administrador de nombres.
-
Haga clic en el botón Nuevo. Excel muestra el cuadro de diálogo Nuevo nombre. (Ver figura 1)
-
En el campo Nombre, ingrese el nombre ListSheets (tenga en cuenta que se trata de una sola palabra, sin espacios).
-
En el campo Se refiere a, ingrese la siguiente fórmula:
-
Haga clic en el botón Aceptar para terminar de crear el rango con nombre. El nuevo rango debería aparecer en el cuadro de diálogo Administrador de nombres.
-
Haga clic en el botón Cerrar para cerrar el cuadro de diálogo Administrador de nombres.
Con la fórmula definida de esta manera, puede ir a la hoja de trabajo en la que desea fusionar todos esos rangos. (Digamos que el nombre de esta hoja de trabajo es «Resumen»). Le sugiero que se asegure de que esta hoja de trabajo sea la última en su libro de trabajo. En la hoja de trabajo Resumen, coloque la siguiente fórmula en la columna A de cualquier fila:
=INDIRECT(INDEX(ListSheets,ROWS($A$1:$A1))&"!"&CELL("address",A$146))
Copie la fórmula, sin embargo, se necesitan muchas filas para representar todas las hojas de trabajo. En otras palabras, si tiene 25 hojas de trabajo (sin contar la hoja de trabajo Resumen), copie la fórmula en 24 filas. Contando el original, ahora debería aparecer la fórmula en un total de 25 filas.
Una nota al margen aquí: La fórmula ListSheets, la que definió en el Administrador de nombres, devuelve una matriz de nombres de hojas de trabajo. La función FILAS se usa para determinar qué elemento de esa matriz se devuelve a través de la función INDICE. Si su hoja de trabajo de Resumen no es la última en su libro de trabajo, ListSheets puede devolverla fácilmente y terminará extrayendo valores de ella. Sin duda, esto es algo que no desea hacer, por lo que sugerí asegurarse de que Resumen fuera la última hoja de trabajo del libro.
Ahora simplemente copie las fórmulas de la columna A a la derecha para que las tenga hasta la columna O. El resultado es que tendrá los valores de A146: O146 en las celdas que contienen las fórmulas.
Anteriormente dije que creo que un enfoque basado en macros es la mejor opción.
Aquí hay una pequeña macro que demuestra por qué este es el caso.
Sub CopyRange() Dim w As Worksheet Dim sRange As String Dim lRow As Long sNewName = "Summary" 'Name for summary worksheet sRange = "A146:O146" 'Range to copy from each worksheet Worksheets(1).Select Worksheets.Add ActiveSheet.Name = sNewName lRow = 2 For Each w In Worksheets If w.Name <> sNewName Then 'Comment out the following line if you don't want to 'include worksheet names in the summary sheet Cells(lRow, 1) = w.Name 'If you commented out the previous line, make a change 'in the following line: change (lRow, 2) to (lRow, 1) w.Range(sRange).Copy Cells(lRow, 2) lRow = lRow + 1 End If Next w End Sub
Tenga en cuenta que hay dos variables (sNewName y sRange) que se establecen al principio de la macro. Estos representan el nombre que desea usar para la nueva hoja de trabajo de resumen creada por la macro y el rango de celdas que desea copiar de cada hoja de trabajo.
Luego, la macro activa la primera hoja de trabajo del libro de trabajo y agrega una nueva hoja de trabajo que se utilizará para el resumen. A esta hoja de trabajo se le asigna el nombre que especificó en la variable sNewName. Luego, la macro recorre un bucle comprobando cada una de las otras hojas de trabajo. Siempre que la hoja de trabajo no sea la de resumen, el nombre de la hoja de trabajo se coloca en la columna A de la hoja de resumen y el rango especificado en la variable sRange (A146: O146) se copia en la hoja de trabajo de resumen comenzando en la columna B .
El enfoque macro es rápido y sencillo. Además, si alguna vez necesita rehacer su hoja de resumen, simplemente elimine la anterior y vuelva a ejecutar la macro.
_Nota: _
Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.
link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador]
.
ExcelTips es su fuente de formación rentable en Microsoft Excel.
Este consejo (9812) se aplica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 y Excel en Office 365.