CODEWITHSUNDEEP
vbaexcel
Ken Tng
Ken Tng

Reputation: 11

Unable to call macro

I am using Excel 2010 and I have been unable to call a macro. I have three worksheets, one where my chart (Sheet 1) is displayed and therein lies a table for a dynamic chart, a worksheet for raw data input (Sheet 2) and lastly a worksheet whereby data is formatted from the raw data through formulas (Sheet 3).

The macro to insert the new row in the table is as follows:

Sub SBTrend()
'
' SBTrend Macro
'

'
    Sheets("Sheet 1").Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
    Range("BA10").Select
End Sub

The code that I have to call the macro in Sheet 3 is as follows:

Private Sub Worksheet_Calculate()
Static OldVal As Variant
If Range("A9").Value <> OldVal Then
    OldVal = Range("A9").Value
    Call SBTrend
End If
End Sub

However, I am always faced with the error "Run-time error '91': Object variable or With block variable not set." And when I debug, this line gets highlighted:

Selection.ListObject.ListRows.Add AlwaysInsert:=True

I would really appreciate any and all the help I can get. Thank you everyone.

Upvotes: 1

Views: 176

Answers (1)

user6432984
user6432984

Reputation:

You need to reference the ListObjects collection.

If there is only one table on the worksheet you can just reference the table by it's index

With Sheets("Sheet 1")
    .Select
    .ListObjects(1).ListRows.Add AlwaysInsert:=True
End With
Range("BA10").Select

If there are multiple you should reference the table by it's name 

With Sheets("Sheet 1")
    .Select
    .ListObjects("Table1").ListRows.Add AlwaysInsert:=True
End With
Range("BA10").Select

Upvotes: 2

Related Questions