When I create a new Workbook and add sheets to it using another Workbook, the sheets appear in the workbook window but VBA does not recognize them

Question

So what I am trying to do is to create a new workbook every year. A new workbook gets created if there is data from two different years in a certain worksheet of the workbook. When it sees two different years, it creates a new workbook, imports the .bat file into the new workbook, then pastes the information from the new year into the new workbook from the old workbook. Then it adds three more sheets and names them the same way they were named in the old workbook. The problem I am running into is when I run the macro for the new workbook, it says that "Sheet2" is Empty and then throws an error. I know all 4 sheets are there because I see them being added when I have trued to debug the code. But when I go into the VBA editer, all I see under the Microsoft Excel Objects folder is "Sheet1(...) and ThisWorkbook. It should show "Sheet1(...)","Sheet2(...)","Sheet3(...)", and "Sheet4(...)". So something is not being transferred into the VBA editor from the new workbook.

I have tried transferring the old data into another workbook and importing the macro to see if it was workbook specific (it wasn't). I have tried creating a loop to activate each sheet when it runs the macro in the new workbook to see if VBA would recognize it then, and adding a delay before and after I save the new workbook after all the sheets are added using the old workbook macro:

I am trying to avoid having to majorly rewrite it.

    Sub NewYearNewFile(WashN As Variant, savepathname As String, FileYearNumber As Variant) 'This sub is to avoid crossover of data from multiple years in one spreadhseet
    'After copy and paste is complete, then compare the years on sheet one and copy next years data over to a new worksheet--------
    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 'activates sheet 1
    NextRow = Cells(Rows.Count, 1).End(xlUp).Row 'Find the last row# of important data
    FinalColumn = Cells(1, Columns.Count).End(xlToLeft).Column 'finds the rightmost column
    FinalYear = Year(Sheet1.Cells(NextRow, 1).Value) 'finds the year in the final row and uses this for the new filename
    NextRow4 = Sheet4.Cells(Rows.Count, 1).End(xlUp).Row 'Find the last row# of important data

    'the folderpath of the Master Macro File (used when createing a new excel file)
    Dim MMacroFilePath As String 'defines and states the filepath of the stored macto to import
    MMacroFilePath = "L:\MCP\Conformal Coat & Wash\Aquastorm50\DataLogs\Compiled data" & _
    "\Automated Files\THE ULTIMATE MACRO.bas" 'the macro filepath
    FileName = "Wash " & WashN & " Data " & FinalYear & ".xlsm" 'saved filename format
    savepath = savepathname & FileName 'complete file path of the saved excel file

    'Compare the final year of recorded data to the year number in the filename.  If they are not the same, do the following.
    'If they are the same, do nothing and finish the copy and paste portions of the code.
    If FinalYear <> FileYearNumber Then
        'Go to the row 200 before the last one, and begin comparing dates.
        For i = NextRow - 200 To NextRow 'from 200 lines up from the bottom to the bottom row
            If Year(Sheet1.Cells(i, 1).Value) <> CInt(FileYearNumber) Then 'if the year in this row doesnt match the file name year
                Cells(i, 1).Resize(NextRow - i + 1, FinalColumn).Cut 'cut this row and all rows below it
                Set newworkbook = Workbooks.Add 'create a new workbook
                newworkbook.Activate 'activate this workbook
                Set VBProj = Nothing 'clearing the variable
                Set VBProj = ActiveWorkbook.VBProject 'defines the variable
                VBProj.VBComponents.Import MMacroFilePath 'imports this macro into the new workbook
                With newworkbook
                    .Sheets("Sheet1").Paste 'pastes it to the first sheet in the new file
                    .Sheets("Sheet1").Rows(1).EntireRow.Insert 'adds a new row for headers when they get inserted later
                    .Sheets(1).Name = Sheet1.Name
                    .Sheets.Add 'add sheet2 to the workbook
                    .Sheets(1).Name = Sheet2.Name
                    .Sheets.Add 'add sheet3 to the workbook
                    .Sheets(1).Name = Sheet3.Name
                    .Sheets.Add 'add sheet4 to the workbook
                    .Sheets(1).Name = Sheet4.Name
                End With
                GoTo CCheck: 'tells the code to skip looping and goto the end of the sub
            End If
        Next i
    'This moves any data from the Chem Check sheet to the new workbook
    CCheck: 'once the data from sheet one moves over, the code goes here
        For i = NextRow4 - 8 To NextRow4 'from 8 lines up from the bottom to the bottom row
            If IsDate(Sheet4.Cells(i, 1).Value) And Year(Sheet4.Cells(i, 1).Value) <> CInt(FileYearNumber) Then 'if the year in this row doesnt match the file name year
                Sheet4.Cells(i, 1).Resize(NextRow4 - i + 1, FinalColumn).Cut 'cut this row and all rows below it
                newworkbook.Activate 'activate the new workbook
                With newworkbook
                    .Sheets("sheet4").Paste 'pastes it to the fourth sheet in the new file
                    .Sheets("Sheet4").Rows(1).EntireRow.Insert 'adds a new row for headers when they get inserted later
                End With
                GoTo Finish: 'tells the code to skip looping and goto the end of the sub
            End If
        Next i
    Finish: 'the code goes here after it reachers "Goto Finish:"
        Application.Wait Now + #12:00:01 AM#
        newworkbook.SaveAs savepath, 52 'saves new file with the Filepath for the new spreadsheet (52 means ".xlsm")
        Application.Wait Now + #12:00:01 AM#
        newworkbook.Close 'closes  the new workbook
    End If
    End Sub

Answer 1

        .Sheets.Add 'add sheet4 to the workbook
        .Sheets(1).Name = Sheet4.Name

Sheets.Add is a function that returns a reference to the added sheet - you're discarding it. Instead, you need to capture it. Declare a Sheet4 variable of type Worksheeet, and set its reference:

Dim Sheet4 As Worksheet
Set Sheet4 = .Sheets.Add

The problem is that you're assuming that the compiler can understand what's happening at run-time.

It doesn't. The compiler doesn't care about what happens at run-time, it only knows about code and objects that exists at compile-time. The VBA runtime/interpreter cares about run-time.

If Sheet4 doesn't exist at compile-time, then VBA doesn't define a global-scope Sheet4 object variable for you, so referring to Sheet4 in code will inevitably result in code that can't be compiled (and thus can't be executed), at least if Option Explicit is specified.

Without Option Explicit, what's happening is quite more complex.

I presume you're getting an "Object required" run-time error, on the first instruction that refers to any member of a worksheet object that you've created at run-time.

The reason is because without Option Explicit, at compile-time there's no identifier validation, so any typo will happily be compiled. At run-time, when VBA encounters an undeclared variable, it simply defines one on-the-spot, as an implicit Variant that can hold literally anything. Except it won't go as far as to infer that this on-the-fly variable is an object with members - so the runtime blows up and says "I've no idea what this is, it should be an Object, but I'm looking an a Variant/Empty".

---

TL;DR: Specify Option Explicit, and declare a variable for every identifier the compiler complains about (via the Debug ~> Compile VBAProject menu), until the code compiles correctly.

The global object variables (e.g. Sheet1, Sheet2, etc.) you're assuming "come for free with every worksheet" only exist if the object exists at compile-time.