Title listing...

[Zadig Galbaras]

Hi!

Is there a way to list the spreadsheets which were a certain cell contains a
certain value?
f.ex.
List the spreadsheet name if cell D4 contains 2004.
This should result in a list where all spreadsheets that have the value 2004
in D4.

Is this possible?


--

Regards
Zadig Galbaras
A Perturbed Norwegian Agnostic
-----

 

[Norman Jones]

Hi Zadig,

Try:

'=======================>>
Public Sub Tester05()
Dim WB As Workbook
Dim SH As Worksheet
Dim ws As Worksheet
Dim i As Long
Const sStr As String = "MyList"
Const strControlValue As String = "2004"
Set WB = ActiveWorkbook '<<=========== CHANGE

On Error Resume Next `'In case no List sheet exists!
Application.DisplayAlerts = False
WB.Sheets(sStr).Delete 'Delete a pre-existing List (if any!)
Application.DisplayAlerts = True
On Error GoTo 0

Set ws = WB.Sheets.Add

ws.Name = sStr

For Each SH In WB.Sheets
With SH
If CStr(.Range("D4").Value) = strControlValue Then
i = i + 1
ws.Cells(i, 1) = SH.Name
End If
End With
Next SH

End Sub
'<<=======================

 

[Zadig Galbaras]

I'll check this soon...stressed times....puh... got to go.....running low...

--

Regards
Zadig Galbaras
A Perturbed Norwegian Agnostic
-----

Hi Zadig,

Try:

'=======================>>
Public Sub Tester05()
Dim WB As Workbook
Dim SH As Worksheet
Dim ws As Worksheet
Dim i As Long
Const sStr As String = "MyList"
Const strControlValue As String = "2004"
Set WB = ActiveWorkbook '<<=========== CHANGE

On Error Resume Next `'In case no List sheet exists!
Application.DisplayAlerts = False
WB.Sheets(sStr).Delete 'Delete a pre-existing List (if any!)
Application.DisplayAlerts = True
On Error GoTo 0

Set ws = WB.Sheets.Add

ws.Name = sStr

For Each SH In WB.Sheets
With SH
If CStr(.Range("D4").Value) = strControlValue Then
i = i + 1
ws.Cells(i, 1) = SH.Name
End If
End With
Next SH

End Sub
'<<=======================