This isn't a questions, but the code I was able to pull together using
responses from other questions.
The following is the code I used to rename every sheet/tab in a workbook.
Each sheet had it's desired name in it, in a the same cell ("E4").


Sub Rename()

Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
sh.Name = sh.Range("E4").Value
Next sh

End Sub


By doing this I was able refernce "E4" of each sheet back to a main sheet.
This way I can change all tab names by putting the desired name for each tab
in the main sheet, then running this macro.