Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Location
    Annapolis, Maryland, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    dates and tabs (2003/SP2)

    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
    Michael Coleman
    BOMI International

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: dates and tabs (2003/SP2)

    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

  3. #3
    Star Lounger
    Join Date
    Feb 2003
    Location
    Annapolis, Maryland, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dates and tabs (2003/SP2)

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

    Michael
    Michael Coleman
    BOMI International

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •