dates and tabs (2003/SP2)

Topic

New Reply

I’ve got a spreadsheet where each tab is a different project, and the dates for that project are in column B along with other details. I want to add a sheet that finds anything that’s a date in column B in the other sheets, and then list that date with the tab name it’s pulled from next to it. Any thoughts?

Thanks!
Michael

Reply | Quote

Replies

Here’s a macro you can use:

Sub ListDates()
Const strSheetName = “Project Dates”
Dim wshList As Worksheet
Dim wsh As Worksheet
Dim s As Long
Dim m As Long
Dim t As Long
On Error Resume Next
Set wshList = Worksheets(strSheetName)
If Err Then
Set wshList = Worksheets.Add(Before:=Worksheets(1))
Else
wsh.Cells.ClearContents
End If
On Error GoTo 0
wshList.Name = strSheetName
wshList.Range(“A1”) = “Date”
wshList.Range(“B1”) = “Project”
wshList.Range(“A1:B1”).Font.Bold = True
wshList.Range(“A1”).EntireColumn.NumberFormat = “m/d/yyyy”
t = 1
For Each wsh In Worksheets
If Not wsh.Name = wshList.Name Then
m = wsh.Range(“B65535”).End(xlUp).Row
For s = 1 To m
If IsDate(wsh.Range(“B” & s)) Then
t = t + 1
wshList.Range(“A” & t) = wsh.Range(“B” & s)
wshList.Range(“B” & t) = wsh.Name
End If
Next s
End If
Next wsh
wshList.Range(“A1”).EntireColumn.AutoFit
End Sub

Reply | Quote

Hans, you never cease to amaze me. Many, many thanks.

Michael

Reply | Quote