Forum Discussion

Heepatrouble's avatar
Heepatrouble
Copper Contributor
May 10, 2022

Need help with Excel Sheet Name Referencing

I use Microsoft Excel for employee scheduling. I have over 30 locations that I have employees staffed each week. In my workbook, each specific location has its own worksheet. Some employees work multiple locations each week, so they have hours on multiple sheets. If I have employee (John Doe) scheduled for 8 hours at 4 locations, and I have 8 in cell C16 on 4 different sheets, representing 8 hours, but all the other sheets have nothing in cell C16. Is there a way to get excel to tell me which sheets have a number in cell C16? I don't need the totals from all the sheets, I just want the sheet names that have a number in cell C16.

microsoft 365

1 Reply

  • alpha5861's avatar
    alpha5861
    Copper Contributor
    May 25, 2022

    You can use this macro (see code below) to achieve what you want. After running the macro, you will see a sheet named "Summary" (last sheet) with the names of all sheets in which C16 is not empty (column A) en the content of C16 (column B).

     

    Sub macro1()
Dim a As Integer, x As Integer, shname As String, shexist As Boolean
shexist = False: shname = "Summary": a = 1
For x = 1 To Sheets.Count
If Sheets(x).Name = shname Then
shexist = True
Sheets(x).Columns("a:b").ClearContents
Exit For
End If
Next x
If shexist = False Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = shname
End If
With Sheets(shname)
.Range("a1").Value = "Sheet-Name"
.Range("b1").Value = "C16-Value"
.Range("a1:b1").Font.Bold = True
For x = 1 To Sheets.Count
If IsEmpty(Sheets(x).Range("C16")) = False Then
a = a + 1
.Range("a" & a).Value = Sheets(x).Name
.Range("b" & a).Value = Sheets(x).Range("c16").Value
End If
Next x
.Columns("a:b").AutoFit
.Columns(2).HorizontalAlignment = xlCenter
End With
End Sub

Resources