Search all sheets and return sheet name

[njimack]

I have a file (we'll call it Policy List) with a list of insurance policies. Another file (which we'll call Settled) has a sheet for each month in a calendar year, with a list of all policies settled during the month.

I'm trying to write some code which will look for each policy number within Policy List and return the month that it was settled.

I know how to do this when I point the Cells.find command at a particular sheet, but I don't know how I search all 12 sheets, and then return the sheetname that contains the policy number.

Can anyone can point me in the right direction?

Thanks

Neil

 

[Barrie Davidson]

Neil, would something like this work for you?

Dim ws As Worksheet
Dim SettledSheet As String
Dim SettledRange As Range

Set SettledRange = Nothing
For Each ws In ActiveWorkbook.Worksheets
    Set SettledRange = ws.Cells.Find("Me") 'change to your search criteria
    If Not SettledRange Is Nothing Then
        SettledSheet = ws.Name
        Exit For
    End If
Next ws

 

[njimack]

Barrie,

Thanks for the help, and it's a step in the right direction. However, rather than returning the sheet name where the policy no. was found, it always returns the active sheetname - that is, the last sheet I was looking at.

Here's my code (bear in mind it's a work in progress, so I know the loop isn't working yet)

Sub Find_Policy()

Dim Policy As String
Dim Month As String
Dim ws As Worksheet
Dim SettledSheet As String
Dim SettledRange As Range

Status_Change = "WL_GERE1 Status Changes 0305.xls"
CI = "Settled CI Claims 2005.xls"

Set SettledRange = Nothing

n = 2

With Sheets(ActiveSheet.Name)
Range("B2").Select
End_Row = Selection.End(xlDown).Row
End With


Do Until n  > End_Row
Policy = Range("L" & n)
Workbooks(CI).Activate


For Each ws In ActiveWorkbook.Worksheets

Set SettledRange = ws.Cells.find(What:=Policy)

If Not SettledRange Is Nothing Then
    SettledRange = "Not found"
    Exit For
 End If
 Next ws

Month = Left(ActiveSheet.Name, 3) & " " & Right(ActiveSheet.Name, 2)

Workbooks(Status_Change).Worksheets("Sheet1").Activate
Range("A2").Value = Month

n = n + 1
Exit Do


Loop


End Sub

I'll carry on trying to figure it out, but if anyone's got any further suggestions...

 

[Barrie Davidson]

Took the liberty of changing the code, hope I got it right. Does this work?

Option Explicit

Sub Find_Policy()

Dim Policy As String
Dim Month As String
Dim ws As Worksheet
Dim SettledSheet As String
Dim SettledRange As Range
Dim CI As String, Status_Change As String

Status_Change = "WL_GERE1 Status Changes 0305.xls"
CI = "Settled CI Claims 2005.xls"

n = 2
End_Row = Range("B2").End(xlDown).Row
Do Until n > End_Row
    Policy = Range("L" & n)
    Set SettledRange = Nothing
    For Each ws In Workbooks(CI).Worksheets
        Set SettledRange = ws.Cells.Find(What:=Policy)
    Next ws
    If Not SettledRange Is Nothing Then
        SettledSheet = SettledRange.Worksheet.Name
        Month = Left(SettledSheet.Name, 3) & " " & Right(SettledSheet.Name, 2)
    End If
    Workbooks(Status_Change).Worksheets("Sheet1").Cells(n, 1).Value = Month
    Month = ""
    n = n + 1
    Exit Do
Loop

End Sub

 

[njimack]

Barrie,

That's it!!

Thank you very much, you've made my life a lot easier.

Neil

 

[njimack]

While I thought Barrie's solution worked, I still had to add to my code. I think I'm almost there, but I'm getting an "Activate method of Range class failed" error. Here's the code:

Sub Find_Policy()

Dim Policy As String
Dim Month As String
Dim ws As Worksheet
Dim SettledSheet As String
Dim SettledRange As Range
Dim CI As String, Status_Change As String, Death As String
Dim n As Integer
Dim End_Row As Integer

Status_Change = "WL_GERE1 Status Changes 0305.xls"
CI = "Settled CI Claims 2005.xls"
Death = "Settled Deaths 2005.xls"

n = 2
End_Row = Range("B2").End(xlDown).Row



Do While Len(Workbooks(Status_Change).Worksheets("Sheet1").Cells(n, 2)) > 0

'Do
    Policy = Range("L" & n)
    Set SettledRange = Nothing
    
    If Cells(n, 6) = "CFI" Or Cells(n, 6) = "Critical Illness" Then
    For Each ws In Workbooks(CI).Worksheets
        Set SettledRange = ws.Cells.find(What:=Policy)
        If Not SettledRange Is Nothing Then
        Workbooks(CI).Activate
        ws.Cells.find(What:=Policy).Activate
        SettledSheet = ActiveSheet.Name
        End If
    If Not SettledRange Is Nothing Then GoTo Month
    Next ws
    End If
    
    If Cells(n, 6) = "Death" Then
    For Each ws In Workbooks(Death).Worksheets
        Set SettledRange = ws.Cells.find(What:=Policy)
        If Not SettledRange Is Nothing Then
        Workbooks(Death).Activate
        ws.Cells.find(What:=Policy).Activate
        SettledSheet = ActiveSheet.Name
        End If
    If Not SettledRange Is Nothing Then GoTo Month
    Next ws
    End If
    
     
     
    If SettledRange Is Nothing Then
        Month = "Not Found"
    Else
    Workbooks(Status_Change).Activate
        
Month:
        Month = Left(SettledSheet, 3) & " " & Right(SettledSheet, 2)
    End If
    Workbooks(Status_Change).Worksheets("Sheet1").Cells(n, 1).Value = Month
    Month = ""
    n = n + 1

Loop


End Sub

The debugger highlights 'ws.Cells.find(What:=Policy).Activate' when it finds a policy number. I put this line in because I couldn't think of another way of returning the sheetname that contained the policy no. Without this line, the macro returned the activesheet name, not the sheetname that contained the policy no.

 

[Barrie Davidson]

I had this in my code which should give you the worksheet name

SettledSheet = SettledRange.Worksheet.Name

Any reason you took it out? Replace the two lines that read

ws.Cells.Find(What:=Policy).Activate
SettledSheet = ActiveSheet.Name

with the one line of code above and that should do the trick.

Regards,

 

[njimack]

Hmm not sure why I took it out.

Anyway, I've reinstated that line, and it's working.

Thanks very much.