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.
source to share
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
source to share