Excel 2007 macro: Run-time error 91:Object variable or with block variable not set

[DirkPoll]

Hi,

Maybe someone can help me out with this one.
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...

The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.

Hope that i have explained it clear enough....

Thanks,

 

[Norie]

Well I think you've explained the problem, but you haven't posted any of the code.

It's kind of hard to help without seeing that.

One problem I can see could be the use of this 'ActiveSheet.Next.Select'.

 

[DirkPoll]

You're right Norie, this is the code:

Sub AllFolderFiles()

Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim TheFile As String
Dim MyPath As String
Dim newSheetName As String
Dim Filename As String
Dim lRows As Long
Dim WS_Count As Integer
Dim K As Integer
Dim I As Integer
Dim j As Integer
Dim x As Integer

MyPath = Sheets("Instruction").Range("B4").Value
ChDir MyPath
TheFile = Dir("*.xls")
Do While TheFile <> ""
  Set wb = Workbooks.Open(MyPath & "\" & TheFile)
'   Opens the Target file.
'   Retrieves Target File name
    Sheets("Info").Select
    Sheets("List").Visible = True
    Sheets("List").Select
    Range("E9").Select
    Selection.Copy
    Windows("ICC Recon Masterfile-v1.xltm").Activate
    Range("M6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    
'   Copy FX rates to target file
    Sheets("FX Rate").Select
    Cells.Select
    Selection.Copy
    Sheets("Instruction").Select
    Windows(Sheets("Instruction").Range("M6").Value).Activate
    Sheets("Info").Select
    Sheets("FX_Rates").Visible = True
    Sheets("FX_Rates").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
'   Clears columns D through BB.
    Sheets("Info").Select
    Columns("D:BB").Select
    Selection.ClearContents
    Range("A1").Select
    
'   Filters the journal details in master workbook on target file name and copies to targetfile.
    Windows("ICC Recon Masterfile-v1.xltm").Activate
    Sheets("Journal-Details").Select
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AA" & Range("AA1").End(xlDown).Row).AutoFilter Field:=5, Criteria1:="" & _
    Sheets("Instruction").Range("M7").Value & ""
    ActiveSheet.Range("$A$1:$AA" & Range("AA1").End(xlDown).Row).AutoFilter Field:=8, Criteria1:="" & _
    Sheets("Instruction").Range("M8").Value & "", _
        Operator:=xlAnd
    Sheets("Journal-Details").Select
    Range("A1:AA1").Select
        Do
        ActiveCell.Offset(1, 0).Select
        Loop While ActiveCell.EntireRow.Hidden = True
            If ActiveCell.Value <> "" Then
                    Range("A1:AA1").Select
                        Do
                        Range(Selection, Selection.End(xlDown)).Select
'                       ActiveCell.Offset(1, 0).Select
                        Loop While ActiveCell.EntireRow.Hidden = True
                    Selection.Copy
                    GoTo Plak_hier
              
            Else
'           MsgBox ("No journal details to copy.")
            Windows(Sheets("Instruction").Range("M6").Value).Activate
            GoTo Balances
            End If
'           Application.CutCopyMode = False
            
Plak_hier:
        Windows(Sheets("Instruction").Range("M6").Value).Activate
        Sheets("Info").Select
        Range("E1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("A1").Select


'   Filters the AR-AP balances in master workbook on target file name and copies to targetfile.
Balances:
    Windows("ICC Recon Masterfile-v1.xltm").Activate
    Sheets("AR-AP Balance").Select
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$S" & Range("S1").End(xlDown).Row).AutoFilter Field:=3, Criteria1:="" & _
    Sheets("Instruction").Range("M7").Value & ""
    ActiveSheet.Range("$A$1:$S" & Range("S1").End(xlDown).Row).AutoFilter Field:=6, Criteria1:="" & _
    Sheets("Instruction").Range("M8").Value & "", _
        Operator:=xlAnd
    Sheets("AR-AP Balance").Select
    Range("A1:S1").Select
        Do
        ActiveCell.Offset(1, 0).Select
        Loop While ActiveCell.EntireRow.Hidden = True
'       Skip hidden rows
            If ActiveCell.Value <> "" Then
'           If filtered cell has a value
                    Range("A1:S1").Select
                        Do
                        Range(Selection, Selection.End(xlDown)).Select
'                       ActiveCell.Offset(1, 0).Select
                        Loop While ActiveCell.EntireRow.Hidden = True
                    Selection.Copy
                    Sheets("Instruction").Select
                    GoTo Plak_hier2
              
            Else
            Sheets("Instruction").Select
'           MsgBox ("No journal details to copy.")
            Windows(Sheets("Instruction").Range("M6").Value).Activate
            Sheets("Info").Select
            Range("A1").Select
            GoTo Tja
            End If
'           Application.CutCopyMode = False
            
Plak_hier2:
        Windows(Sheets("Instruction").Range("M6").Value).Activate
        Sheets("Info").Select
        Range("AH1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Range("A1").Select


Tja:
'   Inserts formula to create identiefier base on LCO-Acct for details & balances in targetfile.

    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Identifier"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=RC[5]&""-""&RC[6]&""-""&RC[8]&""-""&RC[10]"
    Range("D2").Select
    Selection.Copy
    Range("A1").Select
    Selection.Range("D2:D" & Range("E2").End(xlDown).Row).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    Range("D1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Info").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Info").Sort.SortFields.Add Key:=Range("D2:$D" & Range("D2").End(xlDown).Row), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Info").Sort
        .SetRange Range("D1:$D" & Range("D1").End(xlDown).Row)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("AG1").Select
    ActiveCell.FormulaR1C1 = "Balance ID"
    Range("AG2").Select
    ActiveCell.FormulaR1C1 = "=RC[3]&""-""&RC[4]&""-""&RC[6]&""-""&RC[8]"
    Range("AG3").Select
    Columns("AG:AG").EntireColumn.AutoFit
    Range("AG2").Select
    Selection.Copy
    Range("A1").Select
    Selection.Range("AG2:AG" & Range("AH2").End(xlDown).Row).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    Range("AG1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Info").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Info").Sort.SortFields.Add Key:=Range("AG2:AG" & Range("AG2").End(xlDown).Row) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Info").Sort
        .SetRange Range("AG1:AG" & Range("AG1").End(xlDown).Row)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
        
'    Check on sheet names and create missing
    
    LASTROW = Range("AG" & Rows.Count).End(xlUp).Row
    For Each Cell In Sheets(1).Range("AG2:AG" & LASTROW)
        newSheetName = Cell.Value
        For Each ws In Worksheets
            If ws.Name = newSheetName Or newSheetName = "" Or IsNumeric(newSheetName) Then
'                 MsgBox "Sheet already exists or name " & ws.Name & " is invalid", vbInformation
                GoTo Next_WS
            End If
        Next
        
        Sheets("Info").Select
        Sheets("Template").Visible = True
        Sheets("Template").Select
        Sheets("Template").Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Select
        ActiveSheet.Name = newSheetName
        ActiveSheet.Range("A16").Select
        Selection.Copy
        ActiveSheet.Range("A16").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveSheet.Range("B3:B5").Select
        Selection.Copy
        ActiveSheet.Range("B3:B5").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveSheet.Range("H3").Select
        Selection.Copy
        ActiveSheet.Range("H3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        Application.Goto Sheets(1).Cells(1, 1)
Next_WS:
    Next Cell
    Sheets("Template").Select
    
    Sheets("FX_Rates").Select
    Sheets("FX_Rates").Move Before:=Sheets(1)
    Sheets("List").Select
    Sheets("List").Move Before:=Sheets(1)
    Sheets("Info").Select
    Sheets("Info").Move Before:=Sheets(1)
    
'   Filter the data on the info sheet to fill the recon templates
 
    Sheets("Template").Select
Volgend_WS:
    ActiveSheet.Next.Activate
    On Error GoTo move10

'   Copies the accountstring from the LCO-BU-ACCT-ICO sheet to the info sheet.
    Range("A16").Select
    Selection.Copy
    Sheets("Info").Select
    Range("B8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
'   Filters the journal data on sheet name.
    Sheets("Info").Select
    Range("D1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$D$1:$AE" & Range("AE1").End(xlDown).Row).AutoFilter Field:=18, Criteria1:="<>Revaluation"
    ActiveSheet.Range("$D$1:$AE" & Range("AE1").End(xlDown).Row).AutoFilter Field:=5, Criteria1:="" & _
    Sheets("Info").Range("B8").Value & "", _
        Operator:=xlAnd

    Set rng = ActiveSheet.AutoFilter.Range

    Range("b11").Value = rng.Columns(5). _
    SpecialCells(xlCellTypeVisible).Count - 1
    Sheets(Range("B9").Value).Select
    Range("A104").Select
        If ActiveCell.Value <> "" Then
            ActiveCell.Offset(1, 0).Select
                If ActiveCell.Value <> "" Then
                    Range("A104").End(xlDown).Offset(1, 0).Select
                    Else
                    Range("A105").Select
                End If
            Else
            Range("A104").Select
        End If

        lRows = Range("M7").Value
        If lRows = 0 Then
            GoTo Volgend_WS
            Else
            ActiveCell.Resize(lRows, 1).EntireRow.Insert
        End If
   

'       Copies the data from the needed cells in filter results to applicable sheet.
        Sheets("Info").Select
        Range("T1").Select
        Do
        ActiveCell.Offset(1, 0).Select
        Loop While ActiveCell.EntireRow.Hidden = True
            If ActiveCell.Value <> "" Then
                    Do
                    ActiveCell.Offset(1, 0).Select
                    Loop While ActiveCell.EntireRow.Hidden = True
                        If ActiveCell.Value <> "" Then

                            Range("T1").Select
                            Do
                            ActiveCell.Offset(1, 0).Select
                            Loop While ActiveCell.EntireRow.Hidden = True
                            Range(Selection, Selection.End(xlDown)).Select
                            Selection.Copy
                            GoTo Plakken
                            Else
                            Range("T1").Select
                            Do
                            ActiveCell.Offset(1, 0).Select
                            Loop While ActiveCell.EntireRow.Hidden = True
                            Selection.Copy
                            GoTo Plakken
                        End If
                Else
                Sheets(Range("B9").Value).Select
                Range("A1").Select
            End If
Plakken:
        Sheets(Range("B9").Value).Select
        Range("A104").Select
        If ActiveCell.Value <> "" Then
            Do
            ActiveCell.Offset(1, 0).Select
            Loop While ActiveCell.EntireRow.Hidden = True
                If ActiveCell.Value <> "" Then
                Range("A104").End(xlDown).Offset(1, 0).Select
                Else
                Range("A105").Select
                End If
            Else
            Range("A104").Select
        End If
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
        GoTo Volgend_WS
  
      On Error GoTo move10
        
move10:
    Sheets("Template").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Info").Select
    Sheets("Info").Select
    Range("A1").Select
    MsgBox ("alle tabs doorlopen")
    
'   Sort Sheet names ascending & move info sheet to front

       For I = 1 To Sheets.Count
           For j = 1 To Sheets.Count - 1
               If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
                   Sheets(j).Move After:=Sheets(j + 1)
               End If
           Next j
       Next I
       
    Sheets("Info").Select
    Sheets("Info").Move Before:=Sheets(1)
    
'   List the sheetnames so variances can be added later.
    
    Sheets("Info").Select
    Sheets("List").Visible = True
    Sheets("List").Select
    
    x = 1
    Sheets("List").Range("A:A").Clear
    For Each ws In Worksheets
     Sheets("List").Cells(x, 1) = ws.Name
     x = x + 1

Next ws
    Sheets("List").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("FX_Rates").Select
    ActiveWindow.SelectedSheets.Visible = False
    
'   Save file as LCO-Acct-PXXXX is target folder.
    ActiveWorkbook.SaveAs Filename:= _
        (Sheets("List").Range("E7").Value & ".xls"), FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False

'   Save file as PDF in target folder.
    
    Sheets("Info").Select
    Sheets("List").Visible = True
    Sheets("List").Select
    Range("A1").Select
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        Range("E7").Value & ".pdf", Quality:=xlQualityStandard _
        , IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        False
    Sheets("List").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Info").Select
    
    wb.Close
    TheFile = Dir
Loop

End Sub

As mentioned, i'm failry new to this and therefor this code probably looks like a mess... hope you can see what i'm trying to do?

 

[RoryA]

If you are getting an error on ActiveSheet.Next.Select, then that would imply that you are already at the last sheet in the workbook (so the Next method fails)

 

[DirkPoll]

Hi Rory,

That is correct. That's why in i had the "on Error GoTo move10" in there.
In the first target file this appears to work; when at the last sheet, it goes to move10: for the next step.

Yet, in the second target file, it ends up with the error message and does not continue to move10

Thanks,

 

[RoryA]

Your On Error statement appears after the line where you try to activate the next sheet, so it won't do much good. Additionally, an On error Goto label statement should always take you to an error handling block, not simply jump to another part of your code because you need to have a Resume statement. Otherwise any error that occurs after the move10 label will simply cause a run-time error.

 

[DirkPoll]

Ok, i get the idea on the error block...
Guess i need to come up with a real solution for that.

Thanks!

 

[Norie]

Dirk

I really think you need to review that code.

It's making my head hurt trying to follow it.

The reason for that is all the GoTo-ing, Select/Selection etc.

Once my head has stopped throbbing I'll take another, closer look.

 

[RoryA]

I really think you need to review that code.

It's making my head hurt trying to follow it.

It's not just me then!

 

[DirkPoll]

LOL, like i said, i'm kind of new to this and put this together from things i found on the internet.
And i agree that this code is a mess, but i don't have enough knowledge (yet) to clean it up.

Anyway, i found a (messy again) solution to my problem; i now have the macro make a list of all sheets in the workbook and loop through this list to get the sheetnames where the action needs to be done.

Thanks for your time and the directions on the error.

Cheers,