Copier une feuille et obtenir la feuille résultante de l'objet?

Je crois que j'ai enfin cloué cette question - c'est l'été me rend fou, aussi! Il aurait vraiment été sympa si MS fait Copier retourner un objet de feuille, de même que la méthode Add...

Le truc, c'est l'indice qui VBA alloue une nouvelle feuille copiée est pas déterminé... comme d'autres l'ont noté, cela dépend beaucoup de feuilles masquées. En fait, je pense que l'expression Feuilles(n) est en fait interprété comme "le n-ième visible de la feuille". Donc, sauf si vous écrivez une boucle de test de chaque feuille de la propriété visible, à l'aide de ce code est lourde de danger, à moins que le classeur est protégé de sorte que les utilisateurs ne plaisante pas avec les feuilles de propriété visible. Trop dur...

Ma solution à ce dilemme est:

1. Faire la DERNIÈRE feuille visible (même si temporaire)
2. Copie d'APRÈS cette feuille. Il DOIT avoir de l'indice de Feuilles.Le comte
3. Cacher l'ancien dernière feuille de nouveau, si nécessaire, il aura maintenant
   index des Fiches.Count-1
4. Déplacer la nouvelle feuille à l'endroit où vous voulez vraiment il.

Voici mon code qui semble maintenant être à l'épreuve des 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'ai eu quelque chose comme ceci (H indiquant une feuille masquée)

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

.Copie D'Après:=.Draps(2) crée une nouvelle feuille AVANT la prochaine
VISIBLE de la feuille c'est à dire, il est devenu le nouvel indice 6. PAS à l'indice 3, comme vous vous en doutez.

Espère que ça aide 😉

Mise à JOUR:

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

Mis à jour avec les suggestions de Daniel Labelle:

Pour gérer les éventuelles feuilles masquées, faire de la feuille source visible, de le copier, d'utiliser le ActiveSheet méthode renvoie la référence à la nouvelle feuille, et de 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 exact que des feuilles de calcul masquées cause de la nouvelle feuille de calcul de l'indice de non-séquentiel de chaque côté de la feuille de calcul source. J'ai trouvé que Rachel la réponse de travaux si vous êtes à la copie avant de. Mais vous auriez à régler si vous êtes à la copie après.

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

Comme une préférence, vous pouvez le remplacer:

"Set newSheet = .La précédente" avec "Set newSheet = Application.ActiveSheet".

Espérant que cela sera utile à certains d'entre vous.

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

1. Afficher la dernière feuille (1) si 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. Cacher 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

Ce sera toujours insérer la feuille copiée à la fin du classeur de destination.

Après cela, vous pouvez faire toutes les démarches, renomme, 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 souhaitez que le comportement de l'interface pour être plus semblable à la méthode de Copie (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

Vieux post, mais n'est pas sûre de faire réapparaître les feuilles ou l'ajout de suffixes pour les noms.

C'est mon approche:

Sub DuplicateSheet()
    Dim position As Integer
    Dim wbNewSheet As Worksheet
    position = GetFirstVisiblePostion

    ThisWorkbook.Worksheets("Original").Copy Before:=ThisWorkbook.Sheets(position)
    Set wbNewSheet = ThisWorkbook.Sheets(position)

    Debug.Print "Duplicated name:" & wbNewSheet.Name, "Duplicated position:" & wbNewSheet.Index

End Sub

Function GetFirstVisiblePostion() As Integer
    Dim wbSheet As Worksheet
    Dim position As Integer
    For Each wbSheet In ThisWorkbook.Sheets
        If wbSheet.Visible = xlSheetVisible Then
            position = wbSheet.Index
            Exit For
        End If
    Next
    GetFirstVisiblePostion = position
End Function