View Full Version : Change Sheet name

08-23-2017, 12:45 PM
I am using Excel 2010.
I'm wanting to add several sheets to my workbook and I believe Excel needs to know the current sheet name to add more sheets.
I may be wrong. The problem is that every time I run my program the sheet name changes. So, it's kind of a moving target. I tried

Dim shName As String
Dim currentName As String
ThisWorkbook.Sheets(currentName).Name = shName ' error here
currentName = ActiveSheet.Name
shName = InputBox("What name you want to give for your sheet")
ThisWorkbook.Sheets(currentName).Name = shName

And I can input a name but I get the error "Subscript out of range"
A typical name for the sheet could be V_PMs_Due_0823_133421.
I should add that I am importing data from another source an in house maintenance reporting program. As soon as I
import the data the sheet name changes from sheet1 to V_PMs_Due_0823_133421 or something like it.
Also the other two sheets disappear, Sheet2 and Sheet3.
Doesn't seem like I can capture the sheet name to change it.

Is there another approach to adding more sheets?
Thanks for any help. I hope I was clear enough in my question.

08-23-2017, 01:12 PM

Activesheet.Name = "My New Name"

08-23-2017, 01:31 PM
That worked nicely - thank you!

08-24-2017, 03:17 AM
Sheets.Add(after:=Sheets(Sheets.Count)).Name = InputBox("new sheet name")