Copier la feuille et obtenir l'objet feuille résultant?

Je crois que j'ai finalement cloué ce problème-cela m'a rendu fou, aussi! Cela aurait vraiment été bien si MS avait fait Copy retourner un objet sheet, identique à la méthode Add...

La chose est, l'index que VBA alloue une feuille nouvellement copiée n'est en fait pas déterminé... comme d'autres l'ont noté, cela dépend beaucoup de feuilles masquées. En fait, je pense que L'expression Sheets (n) est réellement interprétée comme "la nième feuille visible". Donc à moins d'écrire une boucle testant chaque feuille propriété visible, l'utilisation de ceci dans le code est lourde de danger, à moins que le classeur ne soit protégé afin que les utilisateurs ne puissent pas jouer avec la propriété sheets visible. Trop dur...

Ma solution à ce dilemme est:

1. rendre visible la dernière feuille (même temporaire)
2. Copiez après cette feuille. Il doit avoir des feuilles d'index.Compter
3. masquer à nouveau l'ancienne dernière feuille, si nécessaire - elle aura maintenant index des Fiches.Compte-1
4. déplacez la nouvelle feuille là où vous voulez vraiment il.

Voici mon code-qui semble maintenant être pare-balles...

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

Dans mon cas, j'avais quelque chose comme ceci (h indiquant une feuille cachée)

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

.Copie D'Après:=.Sheets (2) crée en fait une nouvelle feuille avant la suivante Feuille VISIBLE-à savoir, il est devenu le nouvel index 6. Pas à l'index 3, comme vous pouvez vous y attendre.

J'espère que cela aide ; -)

Une autre solution que j'ai utilisée serait de copier la feuille à un endroit où vous connaissez son index, aka first. De là, vous pouvez facilement avoir une référence pour tout ce dont vous avez besoin, et après que vous pouvez vous déplacer librement à l'endroit où vous voulez.

Quelque Chose comme ceci:

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

Mise à jour:

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

Je me rends compte que ce post a plus d'un an, mais je suis venu ici à la recherche d'une réponse au même problème concernant la copie des feuilles et les résultats inattendus causés par les feuilles cachées. Aucune de vraiment adapté à ce que je voulais principalement en raison de la structure de mon classeur. Essentailly il a un très grand nombre de feuilles et ce qui est affiché est piloté par un utilisateur sélectionnant la fonctionnalité spécifique, plus l'ordre des feuilles visibles était importnat pour moi donc je ne voulais pas jouer avec ceux-ci. Si ma solution finale était de compter sur la Convention de nommage par défaut Excels pour les feuilles copiées, et de renommer explicitement la nouvelle feuille par son nom. Exemple de Code ci-dessous (en aparté, mon classeur a 42 feuilles et seulement 7 sont visibles en permanence, et le after:=Sheets(Sheets.count) Placez ma feuille copiée au milieu des 42 feuilles, en fonction des feuilles visibles à ce moment-là.

        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

Quoi qu'il en soit, posté juste au cas où il serait utile à quelqu'un d'autre

Cela devrait être un commentaire en réponse à @ TimWilliams, mais c'est mon premier post donc je ne peux pas commenter.

Ceci est un exemple du problème @RBarryYoung mentionné, lié aux feuilles cachées. Il y a un problème lorsque vous essayez de placer votre copie après la dernière feuille et que la dernière feuille est masquée. Il semble que, si la dernière feuille est cachée, elle conserve toujours l'index le plus élevé, donc vous avez besoin de quelque chose comme

Dim sht As Worksheet

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

Situation similaire lorsque vous essayez de copier avant une première feuille cachée.

Mise à jour avec les suggestions de Daniel Labelle:

Pour gérer d'éventuelles feuilles cachées , rendez la feuille source visible, copiez-la, Utilisez la méthode ActiveSheet Pour renvoyer la référence à la nouvelle feuille et réinitialiser les paramètres de visibilité:

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

Il est correct que les feuilles de calcul masquées rendent le nouvel index de feuille de calcul non séquentiel de chaque côté de la feuille de calcul source. J'ai trouvé que la réponse de Rachel fonctionne si vous copiez avant. Mais vous devrez l'ajuster si vous copiez après.

Une fois que le modèle est visible et copié, le nouvel objet feuille de calcul est simplement la feuille ActiveSheet si vous copiez la source avant ou après.

Comme Préférence, vous pouvez remplacer:

" Définir newSheet = .Précédent "avec" définir newSheet = Application.ActiveSheet".

J'espère que cela est utile à certains d'entre vous.

J'ai essayé de créer une fonction "wrapper" Générique fiable pour la feuille.Méthode de copie pour réutilisation sur plusieurs projets pendant des années.

J'ai essayé plusieurs des approches ici et j'ai trouvé que la réponse de Mark Moore était une solution fiable dans tous les scénarios. C'est-à-dire celui qui utilise le nom "Template (2)" pour identifier la nouvelle feuille.

Dans mon cas, toute solution utilisant la "méthode ActiveSheet" était inutile car dans certains cas, le classeur cible était dans un classeur caché.

De même, certains de mes classeurs ont des feuilles cachées mélangées avec des feuilles visibles à divers endroits; au début, au milieu, à la fin; et donc j'ai trouvé les solutions en utilisant les options Before: Et After: également peu fiables en fonction de l'ordre des feuilles visibles et cachées, avec le facteur supplémentaire lorsque la feuille source est également

Par conséquent, après plusieurs réécritures, j'ai fini avec le wrapper suivant fonction:

'***************************************************************************
'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

NOTE: même cette solution aura des problèmes si le nom de la feuille source est supérieur à 27 caractères, car le nom de la feuille maximale est 31, mais cela est généralement sous mon contrôle.

Basé sur la méthode de Trevor Norman , j'ai développé une fonction pour copier une feuille et renvoyer une référence à la nouvelle feuille.

1. Afficher la dernière feuille (1) si elle n'est pas visible
2. Copie de la feuille source (2) après la dernière feuille (1)
3. Définir la référence à la nouvelle feuille (3), c'est à dire la feuille après la dernière feuille (1)
4. masquer la dernière feuille (1) si nécessaire

Code:

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

Cela insérera toujours la feuille copiée à la fin de classeur de destination.

Après cela, vous pouvez faire des mouvements, des renommages, etc.

Utilisation:

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

Ou si vous voulez que le comportement / l'interface soit plus similaire à la méthode de copie intégrée (c'est-à-dire avant/après), vous pouvez utiliser:

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