VBA Script fails when triggered by Worksheet_Activate()

[alexthecamel]

Hi All,

Quite embarrassed with this one but can't for the life of me fix it.

I have a VBA script that works perfectly when triggered as a Macro, but fails when triggered by Worksheet_Activate.

The line that fails is:

dest.Range("A6:M1000").AutoFilter Field:=pn.Column, Criteria1:=""

Where 'dest' is a previously defined Worksheet and pn is previously defined Range (single cell reference).

The error I get is:

"method 'Find' of object 'Range' failed"

Any gentle kicks in the right direction would be much apprecaited.

Thanks in advance

 

[Norie]

Which line is highlighted if you click Debug when you get the error?

Is that all the code?

Do you have any other code that could be triggered by this code?

 

[alexthecamel]

Thanks for replying!

Apologies copied the wrong line the highlighted line is: Set pn = dest.Range("A1:M1").Find(sName, lookat:=xlPart)
I suspect it's due to my use of dest.Range

more fully the script is (had to redact some bits)

Dim dest As Worksheet
Set dest = ActiveWorkbook.ActiveSheet

Dim source As Worksheet
Set source = ActiveWorkbook.Worksheets("SourceSheetName")

Dim sName As String
sName = ActiveWorkbook.ActiveSheet.Name

Set pn = dest.Range("A1:M1").Find(sName, lookat:=xlPart)

.... loads more code

I can bypass that part but it then fails at the line originally posted, which also uses dest.Range so seems likely somehow related to that, but I can't work out why it works as a manualy triggered Macro but not on Worksheet_Activate()

Thanks again.

 

[Norie]

In that part of the code are you only trying to find which column sName is in?

 

[alexthecamel]

Yes, ultimately I'm looking in that range to find the column sName is in, then will be Autofiltering to just displays blanks in that column and deleting them.

 

[Fluff]

As you're using the activate event, how about

Private Sub Worksheet_Activate()
Dim Pn As Range
Dim source As Worksheet

Set source = ActiveWorkbook.Worksheets("SourceSheetName")

Set Pn = Me.Range("A1:M1").Find(Me.Name, , , xlPart, , , False, , False)
Me.Range("A6:M1000").AutoFilter Field:=Pn.Column, Criteria1:=""
End Sub

 

[alexthecamel]

Thanks Fluff,

Only tested in issolation so will need to integrate with the rest of my code, but certainly does what I asked, thanks for the help.
First time I've come accross this use of 'Me', handy trick.

Thanks also to Norie for replying, as ever the help is very much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback