Add Statement to find out if date have been used already

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Back
Top