herramientas - barras de excel y sus funciones

Copie la hoja y obtenga el objeto hoja resultante?

Question

¿Hay alguna manera fácil / breve de obtener el objeto Excel.worksheet de la nueva hoja que obtiene cuando copia una hoja de trabajo?

ActiveWorkbook.Sheets("Sheet1").Copy after:=someSheet

Resulta que el método .Copy devuelve un booleano en lugar de un objeto de hoja de cálculo. De lo contrario, podría haber hecho:

set newSheet = ActiveWorkbook.Sheets("Sheet1").Copy after:=someSheet    <-- doesn''t work

Entonces, escribí unas 25 líneas de código para obtener el objeto (listé todas las hojas antes de la copia, listé todas las hojas después y sabré cuál está en la última lista solamente. Todas muy largas en VBA), pero estoy buscando una solución más elegante y más corta.

Answer 1

ACTUALIZAR:

Dim ThisSheet As Worksheet
Dim NewSheet As Worksheet
Set ThisSheet = ActiveWorkbook.Sheets("Sheet1")
ThisSheet.Copy
Set NewSheet = Application.ActiveSheet

Answer 2

Actualizado con sugerencias de Daniel Labelle:

Para manejar posibles hojas ocultas , haga visible la hoja fuente, cópiela, use el método ActiveSheet para devolver la referencia a la nueva hoja y restablezca la configuración de visibilidad:

Dim newSheet As Worksheet
With ActiveWorkbook.Worksheets("Sheet1")
    .Visible = xlSheetVisible
    .Copy after:=someSheet
    Set newSheet = ActiveSheet
    .Visible = xlSheetHidden '' or xlSheetVeryHidden
End With

Answer 3

Basado en share , he desarrollado una función para copiar una hoja y devolver una referencia a la nueva hoja.

1. Mostrar la última hoja (1) si no es visible
2. Copie la hoja de origen (2) después de la última hoja (1)
3. Establezca la referencia a la nueva hoja (3), es decir, la hoja después de la última hoja (1)
4. Ocultar la última hoja (1) si es necesario

Código:

Function CopySheet(ByRef sourceSheet As Worksheet, Optional ByRef destinationWorkbook As Workbook) As Worksheet

    Dim newSheet As Worksheet, lastSheet As Worksheet
    Dim lastIsVisible As Boolean

    If destinationWorkbook Is Nothing Then Set destinationWorkbook = sourceSheet.Parent

    With destinationWorkbook
        Set lastSheet = .Worksheets(.Worksheets.Count)
    End With

    lastIsVisible = lastSheet.Visible
    lastSheet.Visible = True

    sourceSheet.Copy After:=lastSheet
    Set newSheet = lastSheet.Next

    If Not lastIsVisible Then lastSheet.Visible = False

    Set CopySheet = newSheet

End Function

Esto siempre insertará la hoja copiada al final del libro de trabajo de destino.

Después de esto, puedes hacer cualquier movimiento, cambiar el nombre, etc.

Uso:

Sub Sample()

    Dim newSheet As Worksheet

    Set newSheet = CopySheet(ThisWorkbook.Worksheets("Template"))

    Debug.Print newSheet.Name

    newSheet.Name = "Sample" '' rename new sheet
    newSheet.Move Before:=ThisWorkbook.Worksheets(1) '' move to beginning

    Debug.Print newSheet.Name

End Sub

O si desea que el comportamiento / interfaz sea más similar al método de Copia integrado (es decir, antes / después), podría usar:

Function CopySheet2(ByRef sourceSheet As Worksheet, Optional ByRef beforeSheet As Worksheet, Optional ByRef afterSheet As Worksheet) As Worksheet

    Dim destinationWorkbook As Workbook
    Dim newSheet As Worksheet, lastSheet As Worksheet
    Dim lastIsVisible As Boolean

    If Not beforeSheet Is Nothing Then
        Set destinationWorkbook = beforeSheet.Parent
    ElseIf Not afterSheet Is Nothing Then
        Set destinationWorkbook = afterSheet.Parent
    Else
        Set destinationWorkbook = sourceSheet.Parent
    End If

    With destinationWorkbook
        Set lastSheet = .Worksheets(.Worksheets.Count)
    End With

    lastIsVisible = lastSheet.Visible
    lastSheet.Visible = True

    sourceSheet.Copy After:=lastSheet
    Set newSheet = lastSheet.Next

    If Not lastIsVisible Then lastSheet.Visible = False

    If Not beforeSheet Is Nothing Then
        newSheet.Move Before:=beforeSheet
    ElseIf Not afterSheet Is Nothing Then
        newSheet.Move After:=afterSheet
    Else
        newSheet.Move After:=sourceSheet
    End If

    Set CopySheet2 = newSheet

End Function

Answer 4

Creo que finalmente me he dado cuenta de este problema, ¡también me ha estado volviendo loco! Realmente hubiera sido bueno si MS hiciera que Copy devolviera un objeto hoja, igual que el método Add ...

La cuestión es que el índice con el que VBA asigna una hoja recién copiada en realidad no está determinado ... como otros han señalado, depende mucho de las hojas ocultas. De hecho, creo que la expresión Hojas (n) se interpreta en realidad como "la enésima hoja visible". Así que a menos que escriba un bucle probando la propiedad visible de cada hoja, usar esto en el código está plagado de peligros, a menos que el libro de trabajo esté protegido para que los usuarios no puedan meterse con las hojas visibles. Demasiado duro...

Mi solución a este dilema es:

1. Hacer visible la última hoja (aunque sea temporal)
2. Copia DESPUÉS de esa hoja. DEBE tener hojas de índice.Count
3. Oculte la última hoja anterior de nuevo, si es necesario; ahora tendrá el índice Sheets.Count-1
4. Mueva la hoja nueva a donde realmente la quiere.

Aquí está mi código, que ahora parece ser a prueba de balas ...

Dim sh as worksheet
Dim last_is_visible as boolean

With ActiveWorkbook
    last_is_visible = .Sheets(.Sheets.Count).Visible
    .Sheets(Sheets.Count).Visible = True
    .Sheets("Template").Copy After:=.Sheets(Sheets.Count)
    Set sh=.Sheets(Sheets.Count)
    if not last_is_visible then .Sheets(Sheets.Count-1).Visible = False 
    sh.Move After:=.Sheets("OtherSheet")
End With

En mi caso, tuve algo como esto (H indica una hoja oculta)

1 ... 2 ... 3 (H) ... 4 (H) ... 5 (H) ... 6 ... 7 ... 8 (H) ... 9 (H)

.Copiar después de: =. Sheets (2) en realidad crea una nueva hoja ANTES de la siguiente hoja VISIBLE - es decir, se convirtió en el nuevo índice 6. NO en el índice 3, como era de esperar.

Espero que ayude ;-)

Answer 5

Es correcto que las hojas de trabajo ocultas provoquen que el nuevo índice de la hoja de cálculo no sea secuencial en ninguno de los lados de la hoja de trabajo de origen. Descubrí que la respuesta de Rachel funciona si estás copiando antes. Pero tendría que ajustarlo si está copiando después.

Una vez que el modelo está visible y copiado, el nuevo objeto de la hoja de trabajo es simplemente el ActiveSheet, ya sea que copie el origen antes o después.

Como preferencia, puede reemplazar:

"Establecer newSheet = .Previous" con "Establecer newSheet = Application.ActiveSheet".

Espero que esto sea útil para algunos de ustedes.

Answer 6

Esto debería ser un comentario en respuesta a @TimWilliams, pero es mi primer mensaje, así que no puedo comentar.

Este es un ejemplo del problema mencionado en @RBarryYoung, relacionado con las hojas ocultas. Hay un problema cuando intenta colocar su copia después de la última hoja y la última hoja está oculta. Parece que, si la última hoja está oculta, siempre conserva el índice más alto, por lo que necesita algo como

Dim sht As Worksheet

With ActiveWorkbook
   .Sheets("Sheet1").Copy After:=.Sheets(.Sheets.Count)
   Set sht = .Sheets(.Sheets.Count - 1)
End With

Situación similar cuando intenta copiar antes de una primera hoja oculta.

Answer 7

He intentado crear una función genérica de "envoltura" genérica para la hoja. Copie el método para su reutilización en varios proyectos durante años.

He intentado varios de los enfoques aquí y he encontrado que solo la respuesta de Mark Moore es una solución confiable en todos los escenarios. Es decir, el que usa el nombre "Plantilla (2)" para identificar la nueva hoja.

En mi caso, cualquier solución que utilizara el "método de ActiveSheet" era inútil ya que en algunos casos el libro de trabajo de destino estaba en un Libro de trabajo no activo u oculto.

Del mismo modo, algunos de mis libros de trabajo tienen hojas escondidas entremezcladas con hojas visibles en varios lugares; al principio, en el medio, al final; y por lo tanto, encontré que las soluciones que usan las opciones Antes: y Después: tampoco son confiables según el orden de las hojas visibles y ocultas, junto con el factor adicional cuando la hoja de origen también está oculta.

Por lo tanto, después de varias reescrituras, he terminado con la siguiente función de envoltura:

''***************************************************************************
''This is a wrapper for the worksheet.Copy method.
''
''Used to create a copy of the specified sheet, optionally set it''s name, and return the new
'' sheets object to the calling function.
''
''This routine is needed to predictably identify the new sheet that is added. This is because
'' having Hidden sheets in a Workbook can produce unexpected results in the order of the sheets,
'' eg when adding a hidden sheet after the last sheet, the new sheet doesn''t always end up
'' being the last sheet in the Worksheets collection.
''***************************************************************************
Function wsCopy(wsSource As Worksheet, wsAfter As Worksheet, Optional ByVal sNewSheetName As String) As Worksheet

    Dim Ws              As Worksheet

    wsSource.Copy After:=wsAfter
    Set Ws = wsAfter.Parent.Sheets(wsSource.Name & " (2)")

    ''set ws Name if one supplied
    If sNewSheetName <> "" Then
        Ws.Name = sNewSheetName
    End If
    Set wsCopy = Ws
End Function

NOTA: Incluso esta solución tendrá problemas si el nombre de la hoja de origen tiene más de 27 caracteres, ya que el nombre máximo de la hoja es 31, pero eso generalmente está bajo mi control.

Answer 8

Me doy cuenta de que esta publicación tiene más de un año, pero vine aquí buscando una respuesta al mismo problema con respecto a la copia de hojas y resultados inesperados causados ​​por hojas ocultas. Ninguno de los anteriores realmente se ajustaba a lo que quería principalmente debido a la estructura de mi libro de trabajo. Essentailly tiene una gran cantidad de hojas y lo que se muestra es impulsado por un usuario que selecciona la funcionalidad específica, además el orden de las hojas visibles era importante para mí, así que no quería meterme con ellas. Así que mi solución final fue confiar en la convención de nomenclatura predeterminada de Excels para las hojas copiadas, y renombrar explícitamente la nueva hoja por su nombre. Ejemplo de código a continuación (como after:=Sheets(Sheets.count) aparte, mi libro de trabajo tiene 42 hojas y solo 7 están permanentemente visibles, y after:=Sheets(Sheets.count) colocan mi hoja copiada en el medio de las 42 hojas, dependiendo de qué hojas son visibles en el tiempo.

        Select Case DCSType
        Case "Radiology"
            ''Copy the appropriate Template to a new sheet at the end
            TemplateRAD.Copy after:=Sheets(Sheets.count)
            wsToCopyName = TemplateRAD.Name & " (2)"
            ''rename it as "Template"
            Sheets(wsToCopyName).Name = "Template"
            ''Copy the appropriate val_Request to a new sheet at the end
            valRequestRad.Copy after:=Sheets(Sheets.count)
            ''rename it as "val_Request"
            wsToCopyName = valRequestRad.Name & " (2)"
            Sheets(wsToCopyName).Name = "val_Request"
        Case "Pathology"
            ''Copy the appropriate Template to a new sheet at the end
            TemplatePath.Copy after:=Sheets(Sheets.count)
            wsToCopyName = TemplatePath.Name & " (2)"
            ''rename it as "Template"
            Sheets(wsToCopyName).Name = "Template"
            ''Copy the appropriate val_Request to a new sheet at the end
            valRequestPath.Copy after:=Sheets(Sheets.count)
            wsToCopyName = valRequestPath.Name & " (2)"
            ''rename it as "val_Request"
            Sheets(wsToCopyName).Name = "val_Request"
    End Select

De todos modos, publicado solo en caso de que sea útil para cualquier otra persona

Answer 9

Otra solución que utilicé sería copiar la hoja en un lugar donde conozca su índice, también conocido como el primero. Ahí puede hacer referencia fácilmente a lo que necesite, y luego puede moverlo libremente a donde desee.

Algo como esto:

Worksheets("Sheet1").Copy before:=Worksheets(1)
set newSheet = Worksheets(1)
newSheet.move After:=someSheet

Answer 10

Dim sht 

With ActiveWorkbook
   .Sheets("Sheet1").Copy After:= .Sheets("Sheet2")
   Set sht = .Sheets(.Sheets("Sheet2").Index + 1)
End With