Change Tag Name Automatically

[Guest]

I have this program that changes the sheet name automatically (see below).
However, I got the following problems:

1.- When one of the cell does not have information the program does not
continue just stop.
2.- If the cell D15, D16 and D17 have number. The program changes these
tag’s names. However, stops as soon find that the next cell is empty.

I need the program to just change the sheet names that have description in
the cells.

Could you please help me with this matter?

Thank in advance.
Maperalia

'****START PROGRAM***********
Option Explicit

Sub RenameSheets()
Dim myDateTime As String

myDateTime = Format(Worksheets("Information").Range("D15").Value,
"mm-dd-yyyy")
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "" & myDateTime & ""

myDateTime = Format(Worksheets("Information").Range("D16").Value,
"mm-dd-yyyy")
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "" & myDateTime & ""

myDateTime = Format(Worksheets("Information").Range("D17").Value,
"mm-dd-yyyy")
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "" & myDateTime & ""

myDateTime = Format(Worksheets("Information").Range("D18").Value,
"mm-dd-yyyy")
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "" & myDateTime & ""

myDateTime = Format(Worksheets("Information").Range("D19").Value,
"mm-dd-yyyy")
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "" & myDateTime & ""

myDateTime = Format(Worksheets("Information").Range("D20").Value,
"mm-dd-yyyy")
Sheets("Sheet6").Select
Sheets("Sheet6").Name = "" & myDateTime & ""

myDateTime = Format(Worksheets("Information").Range("D21").Value,
"mm-dd-yyyy")
Sheets("Sheet7").Select
Sheets("Sheet7").Name = "" & myDateTime & ""

myDateTime = Format(Worksheets("Information").Range("D22").Value,
"mm-dd-yyyy")
Sheets("Sheet8").Select
Sheets("Sheet8").Name = "" & myDateTime & ""

myDateTime = Format(Worksheets("Information").Range("D23").Value,
"mm-dd-yyyy")
Sheets("Sheet9").Select
Sheets("Sheet9").Name = "" & myDateTime & ""

myDateTime = Format(Worksheets("Information").Range("D24").Value,
"mm-dd-yyyy")
Sheets("Sheet10").Select
Sheets("Sheet10").Name = "" & myDateTime & ""

End Sub
'****END PROGRAM***********

 

[Norman Jones]

Hi Maperalia,

Try:

'============>>
Sub TesterX()
Dim SH As Worksheet
Dim rng As Range
Dim i As Long
Dim sStr As String

Set SH = ActiveWorkbook.Sheets("Information")
Set rng = SH.Range("D1524")

On Error Resume Next
For i = 1 To rng.Cells.Count
sStr = Format(rng(i).Value, "mm-dd-yyyy")
Sheets(i).Name = sStr
Next i
On Error GoTo 0

End Sub
'<<============

 

[Guest]

Norman;
Thanks for your quick response and your advice.

I did the following:
1.- I typed the date in the cells D15, D16, D17 then I ran the program and
it is working very well.

2.- I have sheets under the names; Input, Data, Sheet1, Sheet2,
Sheet3,……..Sheet10.
The program renamed the Input, Data, and Sheet1. I wonder if the program can
rename just the sheet# only; starting from Sheet1 to Sheet 10 and keep the
Input and Data names.

3.- Then after I did the step 1. I typed the date in the cell D18 and I got
the following error message:
Run-time error’9’:
Subscript out of range
Then I click debug it is highlighting at :
Set SH = ActiveWorkbook.Sheets("Information")

Do you think it is possible to make adjust it.
I will really appreciate it.

Best regards.
Maperalia

 

[Norman Jones]

Hi Maperalia,

1.- I typed the date in the cells D15, D16, D17 then I ran the program and
it is working very well.
Good.

2.- I have sheets under the names; Input, Data, Sheet1, Sheet2,
Sheet3,....Sheet10.
The program renamed the Input, Data, and Sheet1. I wonder if the program
can
rename just the sheet# only; starting from Sheet1 to Sheet 10 and keep the
Input and Data names.

Try the revised version below.

3.- Then after I did the step 1. I typed the date in the cell D18 and I
got
the following error message:
Run-time error'9':
Subscript out of range
Then I click debug it is highlighting at :
Set SH = ActiveWorkbook.Sheets("Information")

That is because the Information sheet had been renamed when you first ran
the macro. The revised version will only rename sheets with names from
Sheet1 ===> Sheet10, so you shoiuld not experience this problem.

'============
Sub TesterY()
Dim SH As Worksheet
Dim rng As Range
Dim i As Long
Dim sStr As String

Set SH = ActiveWorkbook.Sheets("Information")
Set rng = SH.Range("D1524")

On Error Resume Next
For i = 1 To rng.Cells.Count
sStr = Format(rng(i).Value, "mm-dd-yyyy")
Sheets("Sheet" & i).Name = sStr
' Sheets(i).Name = sStr
Next i
On Error GoTo 0

End Sub
'<<============

 

[Guest]

Norman;
Thank you very much it is working PERFECTLY!!!!.
I really appreciate your helping me with this matter.

Best regards.
Maperalia