Variable not Defined: Sheet and/or Worksheet

[stevenblanc]

Hey folks,

Not entirely sure whats going on. I keep getting the 'variable not defined error' in the following code:

Sub Aggregate_Click()
Dim MyPath, MyFilename As String
Dim vNames() As Variant
Dim i, intSheetCount As Integer

    If Worksheets(1).Cells(14, 15).Value = "1" Then intSheetCount = 2
    If Worksheets(1).Cells(14, 15).Value = "2" Then intSheetCount = 8

    Call ClearImportData

    MyPath = Worksheets(1).Cells(14, 5).Value
    
    ' Add a slash at the end of the path if needed.
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If
    
    MyFilename = Dir(MyPath & "*.xl*")


    Do While MyFilename <> ""

        Workbooks.Open Filename:=MyPath & MyFilename, ReadOnly:=True
        
        i = -1
        
        For Each [B]Sheet[/B] In ActiveWorkbook.Sheets
            If Sheet.Index > intSheetCount Then
                i = i + 1
                ReDim Preserve vNames(i)
                vNames(i) = Sheet.Name
            End If
        Next
        
        Application.DisplayAlerts = False
        ActiveWorkbook.Sheets(vNames).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Application.DisplayAlerts = True
        
        Workbooks(MyFilename).Close savechanges:=False
        MyFilename = Dir()

    Loop
    
    ' This procedure will ensure that all links to Department Guidelines are re-routed to this workbook.
    ' Call ChangeLinks
    Call BreakLinks
    
End Sub

The error is on the bolded 'sheet' above. I tried changing it to worksheet and I get the same error. I'm fairly certain when I saved the file yesterday afternoon it was working perfectly. I have no idea what has cracked.

Any ideas?

Steven

PS: I attempted to step through the code but instantly get the error.

 

[Rx_]

Posted a solution on your MS Access location post.
If it works for you, please post your solution over here so it can help others.

 

[stevenblanc]

Not quite solved. Progress. But a new error popped up which I'm not sure whether or not it's related. Ill post the final results over here.

 

[Rx_]

If you can post a dummy excel workbook with the code and one with your example - I will try to look at it Friday morning.
Hit the Thanks button in my post so I will have a reminder in my mailbox tomorrow morning.

 

[stevenblanc]

I hope this works still. I had to wipe a lot of the information for security reasons. I'll test the aggregation in the morning to see if it still works. A department will typically have 10-20 sheets per period. I've tested it before with 150+ but I cant get it to work right now with 30+.

If you need a sample data sheet I can wipe one for you in the morning. Let me know! It's really strange to see the sheets with all the text wiped.

 

[stevenblanc]

I can't actually post one with my example as it won't let me save my example. If I hit my clear data button and save its basically back to that sheet.

 

[Rx_]

with out data, I conjured up a spread sheet - and kind of forced the code.
It looked as if the array of type variant has a data mismatch type.
A very quick glance, it appears to be building an array of names.
This seemed to satisfy the code to move ahead. It went on to add a sheet 3 to my Aggregation worksheet.


'Dim vNames() As Variant
Dim vNames() As String

suggested debug.print statements to add
For Each MySheet In ActiveWorkbook.Sheets
Debug.Print "MySheet name in the loop is: " & MySheet.Name
If MySheet.Index > intSheetCount Then
Debug.Print "CurrentSheet Count is " & MySheet.Index & " of " & intSheetCount

Look at the debug.print statements
They consist of a string - the "&" and a variable
As the code steps through - it will display some values in the immediate (debug) window (control + G to make that show)