I have an Excel 2010 workbook for financial records. On one worksheet I have a menu. On this menu I select a button with an attached macro witch hides the cells which make up the menu and un-hides a transaction input form.
This transaction input for 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 insure 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.
.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 2012-04-03 21:37
.Select
http://stackoverflow.com/questions/9916342/run-time-error-1004-select-method-of-range-class-failed-vba-2003. Now regarding the Selection
, how does Excel Know, which table you are referring to and in which sheet? : - Siddharth Rout 2012-04-03 21:44
Selection
, Select
, SmallScroll
etc. You have to explicitly tell it which table (and in which sheet) you are referring to. For example refer to the code Worksheets("Transaction History").ListObjects("Table9")
:) Any chance you can upload your excel file? If yes, then you can upload it in wikisend.com and then share the link here : - Siddharth Rout 2012-04-03 22:00
Worksheets("Transaction History").ListObjects("thistory").ListRows.Add (1)
Siddharth Rout 2012-04-03 22:14
Worksheets("Transaction History").ListObjects("thistory").ListRows.Add(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
SpeedCrazy 2012-04-03 22:18
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