Excel Run-time error '91' while executing

Question

I have an Excel 2010 workbook for financial records. On one worksheet, I have a menu. In this menu, I select a button with an attached macro, which hides the cells which make up the menu and un-hides a transaction input form.

This transaction input form contains several values that are of no consequence here. After entering the data you hit the appropriately labeled enter button. Now when I wrote the doc this button worked fine; it went to the records worksheet, inserted a blank row in the correct table, went back to the form, copied the data, went back to the new blank row, and inserted the data. It then hit sort newest to oldest on the date column to ensure entries are in the correct order. Finally it heads back to the form and clears the data. This worked fine until I decided to hide the history sheet.

When I decided to hide the sheet I re-recorded the macro un-hiding the sheet preforming the operations and re-hiding the sheet. Now when I run the macro it un-hides the sheet. It then hangs with a "Runtime error '91' Object variable or With block variable not set" error. It does not complete the macro, leaving the history sheet visible and unchanged with the form still full of data.

Selecting debug in the options given when the error appears shows this:

  Sub transaction()
'
' transaction Macro
'

'
    Sheets("Income").Select
    Sheets("Transaction History").Visible = True
    Selection.ListObject.ListRows.Add (1)
    Sheets("Income").Select
    Range("B6:G6").Select
    Selection.Copy
    Sheets("Transaction History").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Transaction History").ListObjects("Table9").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Transaction History").ListObjects("Table9").Sort. _
        SortFields.Add Key:=Range("Table9[[#All],[Date]]"), SortOn:=xlSortOnValues _
        , Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Transaction History").ListObjects("Table9"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("Transaction History").Select
    ActiveWindow.SelectedSheets.Visible = False
    Range("B6:G6").Select
    Selection.ClearContents
    Rows("6:8").Select
    Range("A8").Activate
    Selection.EntireRow.Hidden = False
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "=R[1]C"
    Rows("7:7").Select
    Selection.EntireRow.Hidden = True
    Range("C6").Select
End Sub

With the row

Selection.ListObject.ListRows.Add (1)

Highlighted in yellow. I do not know any VBA, hence recording macros not writing them, and i can't make heads nor tales of this.

Edit: Okay I have firgured out how to reference the table:

Worksheets("Transaction History").ListObjects("thistory").ListRows.Add (1)

So from there on I am just going to re-write the whole thing, gonna try and do it in VBA rather than record it. Google is my friend. Thanks, for all your help. Any help you continue to give is greatly apreciated.

Just realized it actually hangs before inserting the blank row. — SpeedCrazy
.Select is the major cause of errors :) You should directly perform the operations. Now regarding Selection.ListObject.ListRows.Add (1) Which sheet is the Selection in and what exactly is that Selection? — Siddharth Rout
Sorry for my n00bishness here but what exactly do you mean "Directly Preform the operations"? The rest of your comment is just as confusing to me. Sorry — SpeedCrazy
Oh wait... are you saying instead of selecting the table and adding a row to it i should directly say tableX add 1 row? (however that is said in VBa)) — SpeedCrazy
Yup :) See this thread on how to avoid using .Select stackoverflow.com/questions/9916342/…. Now regarding the Selection, how does Excel Know, which table you are referring to and in which sheet? :) — Siddharth Rout

Siddharth Rout Siddharth Rout · Accepted Answer · 2012-04-03T22:23:21

Is this what you are trying?

Sub transaction()
    Dim wbI As Worksheet, wbO As Worksheet

    Set wbI = Sheets("Income")
    Set wbO = Sheets("Transaction History")

    With wbO
        .Visible = True

        .ListObjects("thistory").ListRows.Add (1)

        wbI.Range("B6:G6").Copy

        .Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

        With .ListObjects("thistory").Sort
            With .SortFields
                .Clear
                .Add Key:=Range("thistory[[#All],[Date]]"), SortOn:=xlSortOnValues _
                , Order:=xlDescending, DataOption:=xlSortNormal
            End With
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

        .Visible = False
   End With

   With wbI
        .Range("B6:G6").ClearContents
        .Rows("6:8").Hidden = False
        .Range("B6").FormulaR1C1 = "=R[1]C"
        .Rows(7).Hidden = True
   End With

End Sub

Excel Run-time error '91' while executing

1 Answers