listi.net

Upcoming Sheet List

This code will create a new sheet with links to sheets with a specific prefix.

In this model, I have a sheet that has many date-based copies and I have to switch the name to a standard name the code is looking for to send emails that changes based on the date.

I need this to easily find these sheets out of the more than 100 sheets I have.


Sub GetAllUpcomingSheets()

CreateSheet "UpcomingSheets"
Range("A1") = "Sheet Name"
Range("B1") = "Date of Data"
Range("C1") = "Items"
Range("D1") = "gng or gcpar"

For Each s In Sheets
    'What if hidden?
    s.Select
    sName = LCase(ActiveSheet.name)
    If Left(sName, 13) = "de upcoming 2" then
        sDate = [r2] ' cell r2 has the date of the release
        lItems = FindLastRow(1)
        gngCount = [=COUNTIF(y:y,"*gng*")]
        gcparcount = [=COUNTIF(Y:Y,"*gcp*")]

        Sheets("UpcomingSheets").Select
        r = FindLastRow(1)
        Cells(r + 1, 1) = sName
        Cells(r + 1, 2) = sDate
        Cells(r + 1, 3) = lItems - 1
        if gngcount = 0 and gcparcount <> 0 Then
           sCount = "gcpar=" & gcparcount
        elseif gngcount <> 0 And gcparcount = 0 Then
           sCount "gng=" & gngcount
        else
           sCount = "gng=" & gngCount & vbLF & "gcpar=" & gcparcount
        End If
        cells(r + 1, 4) = sCount
    End If 
Next
Sheets("UpcomingSheets").Select
r = FindLastRow(1)
For i  = 2 To r
    Cells(i, 1).Select
    sSheet = Cells(i,1)
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'" & sSheet & "'!A1, TextToDisplay:=sSheet

Next
Columns("A:B").EntireColumn.AutoFit
End Sub