I know how to rename a sheet when I already know what the sheet name is going to be. For example I geta weekly file from an external source and I rename it as follows.
'Rename sheet in Study Summary
Workbooks.Open Filename:= _
"G:\Database\Database files\Study Summary.xlsx"...