Add Statement to find out if date have been used already

[maperalia]

I have a program that create a new sheet from a template sheet and rename it
according the date typed in cell G7 of the template sheet (see program below).
The G7 is taken from the row 9 ,colunms “E” to “U” of the file named
Time.xls on the sheet1.

The program is running without problem, however, I want to add a statement
that reads all the columns mention above in the row 9 and stop when noticed
that the date have not been used yet and then continue with the program to
create a new sheet.

Do you think is possible to do this?
Thanks in advance.
Maperalia





Sub ChangeTagName()
Dim SH As Worksheet
Dim rng As Range
Dim i As Long
Dim sStr As String


'*********** OPEN TEMPLATE FILE ************************************
Workbooks.Open Filename:="C:\TimeCard\Timecard.xls"
Sheets("Template").Select
'***********************************************************************

'*****COPY TEMPLATE SHEET AND RENAME IT AS A "SHEET" ***********
Sheets("Template").Copy After:=Sheets(2)
Sheets("Template (2)").Select
Sheets("Template (2)").Name = "Sheet"
'***********************************************************************

'************* RENAME TAG NAME TO ACTUAL DATE******************
Set SH = ActiveWorkbook.Sheets("Sheet")
Set rng = SH.Range("G7")
On Error Resume Next
For i = 1 To rng.Cells.Count
sStr = Format(rng(i).Value, "mm-dd-yyyy")
Sheets("Sheet").Name = sStr
'***********************************************************************

Next i
On Error GoTo 0

End Sub

[JNW]

You could try this. I basically added another loop through the sheets and an
if statement.

Let me know if it works (or more importantly if it doesn't!) and we can go
from there.


Sub ChangeTagName()
Dim SH As Worksheet
Dim rng As Range
Dim i As Long
Dim sStr As String


'*********** OPEN TEMPLATE FILE ************************************
Workbooks.Open Filename:="C:\TimeCard\Timecard.xls"
Sheets("Template").Select
'***********************************************************************

'*****COPY TEMPLATE SHEET AND RENAME IT AS A "SHEET" ***********
Sheets("Template").Copy After:=Sheets(2)
Sheets("Template (2)").Select
Sheets("Template (2)").Name = "Sheet"
'***********************************************************************

'************* RENAME TAG NAME TO ACTUAL DATE******************
Set SH = ActiveWorkbook.Sheets("Sheet")
Set rng = SH.Range("G7")
On Error Resume Next
For i = 1 To rng.Cells.Count
''''New stuff \/ \/
For each sh in worksheets
if sh.name = i then
msgbox ("This date is already used.")
else:
sStr = Format(rng(i).Value, "mm-dd-yyyy")
Sheets("Sheet").Name = sStr
end if
next sh
'***********************************************************************

Next i
On Error GoTo 0

End Sub

"maperalia" wrote:

> I have a program that create a new sheet from a template sheet and rename it
> according the date typed in cell G7 of the template sheet (see program below).
> The G7 is taken from the row 9 ,colunms ā€œEā€ to ā€œUā€ of the file named
> Time.xls on the sheet1.
>
> The program is running without problem, however, I want to add a statement
> that reads all the columns mention above in the row 9 and stop when noticed
> that the date have not been used yet and then continue with the program to
> create a new sheet.
>
> Do you think is possible to do this?
> Thanks in advance.
> Maperalia
>
>
>
>
>
> Sub ChangeTagName()
> Dim SH As Worksheet
> Dim rng As Range
> Dim i As Long
> Dim sStr As String
>
>
> '*********** OPEN TEMPLATE FILE ************************************
> Workbooks.Open Filename:="C:\TimeCard\Timecard.xls"
> Sheets("Template").Select
> '***********************************************************************
>
> '*****COPY TEMPLATE SHEET AND RENAME IT AS A "SHEET" ***********
> Sheets("Template").Copy After:=Sheets(2)
> Sheets("Template (2)").Select
> Sheets("Template (2)").Name = "Sheet"
> '***********************************************************************
>
> '************* RENAME TAG NAME TO ACTUAL DATE******************
> Set SH = ActiveWorkbook.Sheets("Sheet")
> Set rng = SH.Range("G7")
> On Error Resume Next
> For i = 1 To rng.Cells.Count
> sStr = Format(rng(i).Value, "mm-dd-yyyy")
> Sheets("Sheet").Name = sStr
> '***********************************************************************
>
> Next i
> On Error GoTo 0
>
> End Sub
>