Lorsque je crée un nouveau classeur et ajoute des feuilles à partir d'un autre classeur, les feuilles apparaissent dans la fenêtre du classeur mais VBA ne les reconnaît pas

Question

Donc, ce que j'essaie de faire, c'est de créer un nouveau classeur chaque année. Un nouveau classeur est créé s'il y a des données de deux années différentes dans une certaine feuille de calcul du classeur. Lorsqu'il voit deux années différentes, il crée un nouveau classeur, importe le fichier .bat dans le nouveau classeur, puis colle les informations de la nouvelle année dans le nouveau classeur à partir de l'ancien classeur. Ensuite, il ajoute trois autres feuilles et les nomme de la même manière qu'elles étaient nommées dans l'ancien classeur. Le problème auquel je suis confronté est lorsque j'exécute le macro pour le nouveau classeur, il dit que "Sheet2" est vide puis lance une erreur. Je sais que les 4 feuilles sont là parce que je les vois ajoutées lorsque j'ai essayé de déboguer le code. Mais lorsque je vais dans l'éditeur VBA, tout ce que je vois sous le dossier Objets Microsoft Excel est "Sheet1(...)" et ThisWorkbook. Il devrait afficher "Sheet1(...)","Sheet2(...)","Sheet3(...)", et "Sheet4(...)". Donc quelque chose n'est pas transféré dans l'éditeur VBA depuis le nouveau classeur.

J'ai essayé de transférer les anciennes données dans un autre classeur et d'importer le macro pour voir s'il était spécifique au classeur (ce n'était pas le cas). J'ai essayé de créer une boucle pour activer chaque feuille lorsqu'il exécute le macro dans le nouveau classeur pour voir si VBA le reconnaîtrait alors, et d'ajouter un délai avant et après avoir enregistré le nouveau classeur après avoir ajouté toutes les feuilles en utilisant le macro de l'ancien classeur:

J'essaie d'éviter d'avoir à le réécrire en profondeur.

    Sub NewYearNewFile(WashN As Variant, savepathname As String, FileYearNumber As Variant) 'Cette sous procédure vise à éviter le chevauchement de données de plusieurs années dans une seule feuille de calcul
    'Après que la copie et le collage sont terminés, comparer les années sur la feuille un et copier les données de l'année suivante sur une nouvelle feuille de calcul--------
    Dim NextRow As Integer, FinalColumn As Integer, FinalYear As String
    Dim i As Long, newworkbook As Workbook, NextRow4 As Integer
    Dim VBProj As Object
    Dim savepath As String
    Dim FileName As String

    Sheet1.Activate 'active la feuille 1
    NextRow = Cells(Rows.Count, 1).End(xlUp).Row 'Trouve le dernier n° de ligne des données importantes
    FinalColumn = Cells(1, Columns.Count).End(xlToLeft).Column 'trouve la colonne la plus à droite
    FinalYear = Year(Sheet1.Cells(NextRow, 1).Value) 'trouve l'année dans la dernière ligne et l'utilise pour le nouveau nom de fichier
    NextRow4 = Sheet4.Cells(Rows.Count, 1).End(xlUp).Row 'Trouve le dernier n° de ligne des données importantes

    'le chemin du dossier du Fichier Macro Maître (utilisé lors de la création d'un nouveau fichier Excel)
    Dim MMacroFilePath As String 'définit et indique le chemin d'accès du macro stocké à importer
    MMacroFilePath = "L:\MCP\Conformal Coat & Wash\Aquastorm50\DataLogs\Compiled data" & _
    "\Automated Files\THE ULTIMATE MACRO.bas" 'le chemin d'accès du macro
    FileName = "Wash " & WashN & " Data " & FinalYear & ".xlsm" 'format du nom de fichier enregistré
    savepath = savepathname & FileName 'chemin complet du fichier Excel enregistré

    'Comparer l'année finale des données enregistrées au numéro de l'année dans le nom de fichier. Si elles ne sont pas identiques, faire ce qui suit.
    'Si elles sont identiques, ne rien faire et terminer les parties de copie et de collage du code.
    If FinalYear <> FileYearNumber Then
        'Aller à la ligne 200 avant la dernière et commencer à comparer les dates.
        For i = NextRow - 200 To NextRow 'de 200 lignes en haut jusqu'à la dernière ligne
            If Year(Sheet1.Cells(i, 1).Value) <> CInt(FileYearNumber) Then 'si l'année dans cette ligne ne correspond pas à l'année du nom de fichier
                Cells(i, 1).Resize(NextRow - i + 1, FinalColumn).Cut 'coupe cette ligne et toutes les lignes en dessous
                Set newworkbook = Workbooks.Add 'créer un nouveau classeur
                newworkbook.Activate 'activer ce classeur
                Set VBProj = Nothing 'effacer la variable
                Set VBProj = ActiveWorkbook.VBProject 'définit la variable
                VBProj.VBComponents.Import MMacroFilePath 'importe ce macro dans le nouveau classeur
                With newworkbook
                    .Sheets("Sheet1").Paste 'colle-le dans la première feuille du nouveau fichier
                    .Sheets("Sheet1").Rows(1).EntireRow.Insert 'ajoute une nouvelle ligne pour les en-têtes lors de leur insertion ultérieure
                    .Sheets(1).Name = Sheet1.Name
                    .Sheets.Add 'ajoute la feuille2 au classeur
                    .Sheets(1).Name = Sheet2.Name
                    .Sheets.Add 'ajoute la feuille3 au classeur
                    .Sheets(1).Name = Sheet3.Name
                    .Sheets.Add 'ajoute la feuille4 au classeur
                    .Sheets(1).Name = Sheet4.Name
                End With
                GoTo CCheck: 'indique au code de sauter la boucle et d'aller à la fin de la sous-procédure
            End If
        Next i
    'Cela déplace les données de la feuille Chem Check vers le nouveau classeur
    CCheck: 'une fois que les données de la feuille un sont transférées, le code va ici
        For i = NextRow4 - 8 To NextRow4 'de 8 lignes en haut à la dernière ligne
            If IsDate(Sheet4.Cells(i, 1).Value) And Year(Sheet4.Cells(i, 1).Value) <> CInt(FileYearNumber) Then 'si l'année dans cette ligne ne correspond pas à l'année du nom de fichier
                Sheet4.Cells(i, 1).Resize(NextRow4 - i + 1, FinalColumn).Cut 'coupe cette ligne et toutes les lignes en dessous
                newworkbook.Activate 'activer le nouveau classeur
                With newworkbook
                    .Sheets("sheet4").Paste 'colle-le dans la quatrième feuille du nouveau fichier
                    .Sheets("Sheet4").Rows(1).EntireRow.Insert 'ajoute une nouvelle ligne pour les en-têtes lors de leur insertion ultérieure
                End With
                GoTo Finish: 'indique au code de sauter la boucle et d'aller à la fin de la sous-procédure
            End If
        Next i
    Finish: 'le code va ici après avoir atteint "Goto Finish:"
        Application.Wait Now + #12:00:01 AM#
        newworkbook.SaveAs savepath, 52 'enregistre le nouveau fichier avec le chemin d'accès pour la nouvelle feuille de calcul (52 signifie ".xlsm")
        Application.Wait Now + #12:00:01 AM#
        newworkbook.Close 'ferme le nouveau classeur
    End If
    End Sub

Answer 1

        .Sheets.Add 'ajoute la feuille4 au classeur
        .Sheets(1).Name = Sheet4.Name

Sheets.Add est une fonction qui renvoie une référence à la feuille ajoutée - vous la rejetez. Au lieu de cela, vous devez la capturer. Déclarez une variable Sheet4 de type Worksheet, et définissez sa référence :

Dim Sheet4 As Worksheet
Set Sheet4 = .Sheets.Add

Le problème est que vous supposez que le compilateur peut comprendre ce qui se passe au moment de l'exécution.

Il ne peut pas. Le compilateur se fiche de ce qui se passe au moment de l'exécution, il ne connaît que le code et les objets qui existent au moment de la compilation. Le temps d'exécution/interprétation VBA se charge de l'exécution.

Si Sheet4 n'existe pas au moment de la compilation, alors VBA ne définit pas pour vous une variable d'objet Sheet4 à portée globale, donc faire référence à Sheet4 dans le code aboutira inévitablement à un code qui ne peut pas être compilé (et donc ne peut pas être exécuté), du moins si Option Explicit est spécifié.

Sans Option Explicit, ce qui se passe est beaucoup plus complexe.

Je suppose que vous obtenez une erreur de type "Object requis" à l'exécution, sur la première instruction qui se réfère à un membre d'un objet feuille que vous avez créé à exécution.

La raison en est qu'au moment de la compilation, sans Option Explicit, il n'y a pas de validation des identifiants, donc toute faute de frappe sera heureusement compilée. Au moment de l'exécution, lorsque VBA rencontre une variable non déclarée, il en définit une sur place, en tant que Variant implicite pouvant contenir littéralement n'importe quoi. Sauf qu'il ne va pas jusqu'à inférer que cette variable créée à la volée est un objet avec des membres - donc l'exécution explose et dit "Je ne sais pas ce que c'est, cela devrait être un Object, mais je regarde un Variant/Empty".

---

TL;DR: Spécifiez Option Explicit, et déclarez une variable pour chaque identifiant sur lequel le compilateur se plaint (via le menu Debug ~> Compile VBAProject), jusqu'à ce que le code compile correctement.

Les variables d'objet globales (par exemple, Sheet1, Sheet2, etc.) que vous supposez "venir gratuitement avec chaque feuille de calcul" n'existent que si l'objet existe au moment de la compilation.