I have a code that grabs all the sheet names in the workbook and creates a sheet with the names on it. I am essentially trying to have it loop through the cells until it hits a blank cell, but for the life of me I cannot figure it out. Each cell on Sheet("Test") is a name of a Worksheet. Here is my code.
Would love someone's wisdom on how to get it to work.
Code:
Sub ListWorkSheet()
Dim xWs As Worksheet
Dim strName As String
Dim MyCell, Rng As Range
Const WHAT_FIND1 As String = "Tracker"
Dim FindCellT As Excel.Range
On Error Resume Next
Application.DisplayAlerts = False
xTitleId = "Test"
Application.Sheets(xTitleId).Delete
Application.Sheets.Add Application.Sheets(1)
Set xWs = Application.ActiveSheet
xWs.Name = xTitleId
For i = 2 To Application.Sheets.Count
xWs.Range("A" & (i - 1)) = Application.Sheets(i).Name
Next
Application.DisplayAlerts = True
Set FindCellT = Sheets("Test").Range("A:A").Find(What:=WHAT_FIND1, LookAt:=xlWhole)
T1 = FindCellT.Offset(1, 0).Row
T2 = FindCellT.End(xlDown).Row
Set Rng = Sheets("Test").Range("A" & T1, "A" & T2)
For Each MyCell In Rng
If MyCell <> "" Then
strName = MyCell
Sheets(strName).Range("P1").Value = "Test"
Else
Exit Sub
End If
Next
End Sub
Would love someone's wisdom on how to get it to work.