Error on method intersect

[Beneindias]

Hi to all,

Here I am again to bring my problems with vba.

I have a workbook where I have a few sheets, that all have one table and one pivot table.

To update the data on the pivot table, I have this code, that runs when I change the column "Valor s/ Iva" on the table:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim tbl As ListObject
    Dim tblName As String
    Dim tblColName As String
    
    For Each tbl In ActiveSheet.ListObjects
        'Application.ScreenUpdating = False
   
        'Table name
        tblName = tbl.Name
        'Column name
        tblColName = tblName & "[Valor s/ Iva]"
            
        If Not Intersect(Target, Range(tblColName)) Is Nothing Then
            'Refreshes workbook pivot tables

            MsgBox "Intersected"
            ThisWorkbook.RefreshAll
            MsgBox "Updated"

        End If
        
        'Application.ScreenUpdating = True
        
    Next tbl
    
End Sub

When I run this macro, I get this error:

run-time error '1004': Method 'Intersect' of 'object '_Global' failed

On this line:
If Not Intersect(Target, Range(tblColName)) Is Nothing Then

I have added those two MsgBox, and the first on ("Intersected") shows, but not the second one.
Although I get the error, the data still updates on the pivot tables


Thank you all

 

[HighAndWilder]

Hi to all,

Here I am again to bring my problems with vba.

I have a workbook where I have a few sheets, that all have one table and one pivot table.

To update the data on the pivot table, I have this code, that runs when I change the column "Valor s/ Iva" on the table:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim tbl As ListObject
    Dim tblName As String
    Dim tblColName As String
   
    For Each tbl In ActiveSheet.ListObjects
        'Application.ScreenUpdating = False
  
        'Table name
        tblName = tbl.Name
        'Column name
        tblColName = tblName & "[Valor s/ Iva]"
           
        If Not Intersect(Target, Range(tblColName)) Is Nothing Then
            'Refreshes workbook pivot tables

            MsgBox "Intersected"
            ThisWorkbook.RefreshAll
            MsgBox "Updated"

        End If
       
        'Application.ScreenUpdating = True
       
    Next tbl
   
End Sub

When I run this macro, I get this error:

run-time error '1004': Method 'Intersect' of 'object '_Global' failed

On this line:
If Not Intersect(Target, Range(tblColName)) Is Nothing Then

I have added those two MsgBox, and the first on ("Intersected") shows, but not the second one.
Although I get the error, the data still updates on the pivot tables


Thank you all

As you only have one table and one pivot table the loop is not needed.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

  If Target.CountLarge > 1 Then
    Exit Sub
  End If
              
  If Not Intersect(Target, ActiveSheet.ListObjects(1).ListColumns("Valor s/ Iva").DataBodyRange) Is Nothing Then
        
    ActiveSheet.PivotTables(1).PivotCache.Refresh

  End If
  
End Sub

 

[Beneindias]

As you only have one table and one pivot table the loop is not needed.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

  If Target.CountLarge > 1 Then
    Exit Sub
  End If
             
  If Not Intersect(Target, ActiveSheet.ListObjects(1).ListColumns("Valor s/ Iva").DataBodyRange) Is Nothing Then
       
    ActiveSheet.PivotTables(1).PivotCache.Refresh

  End If
 
End Sub

After posting this question, I was exactly thinking about that...I didn't need a loop...

Tried you solution and it worked beautifully

Thank you