Runtime error "91" while executing a macro

I have an Excel 2010 workbook for financial statements. On one sheet, I have a menu. In this menu, I select the button with the attached macro, which hides the cells that make up the menu and does not hide the transaction input form.

This transaction entry contains several values ​​that are irrelevant here. After entering the data, you will press the corresponding input button with the inscription. Now that I wrote the document, this button was working fine, it ended up in the data sheet, which had a blank row inserted in the correct table, returned to the form copied, the data went back to a new blank row and pasted the data. It then hits the earliest of the date columns to ensure that the records are in the correct order. Finally, it goes 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 rewritten the macro without hiding the sheet, preformatting the operations and hiding the sheet. Now when I run the macro it hides the sheet. Then it hangs with the error "Runtime error 91" Object variable or with a block variable error. It does not terminate the macro, leaving the history sheet visible and unchanged with the form still filled with data.

Selecting debug in the options specified when the error occurs shows the following:

  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 line

Selection.ListObject.ListRows.Add (1)

      

Highlighted in yellow. I don't know any VBA, so I write macros that don't write them, and I can't get my heads and stories out of it.

Edit: Ok, I put together the links to the table:

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

      

So from there I just rewrote the whole thing, try to do it in VBA, not write it down. Google is my friend. Thank you for your help. Any help you keep giving is greatly appreciated.

+1


source to share


1 answer


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

      

+2


source







All Articles