Forum Discussion
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 mult...
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