Excel Macro Run-time error '1004': Selected method of Range class failed

[sersan]

Hi all!

I have an Excel workbook with several spreadsheets in it.
In one spreadsheet, TOTALdata, I have the following macro:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B3:AI71"), Range(Target.Address)) Is Nothing Then
Call Sort_DB
End If
End Sub


Sort_DB is a macro that select several ranges of values and copy them inside the same spreadsheet in a different place.
The workbook has a connection to a database to update several data inside the workbook.
When updating the workbook data with the spreadsheet TOTALdata selected, everything runs smoothly. However, if I have any other spreadsheet selected, I get the Run-time error '1004': Selected method of Range class failed.
The odd thing to me is that it doesn't happen only when I run the data update from the mention spreadsheet.

Hope someone can help me with this. Thanks in advance.

[Aflatoon]

It is almost never necessary to select anything in code, but if you are going to select a range, its worksheet must be active first. You haven't posted the code for Sort_DB so it's hard to help further.

[snb]

Should be:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("B3:AI71"), Target) Is Nothing Then Sort_DB
End Sub

[Aflatoon]

Should be:


That's a matter of personal preference surely.

[sersan]

Thanks guys for the attention!
Aflatoon, you got it right on the spot! I just need to activate the worksheet and looks like it's working fine after a few tests. :)
Cheers!