MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA Sheetname Problem Might be tough


Posted by David on April 23, 2001 5:39 AM

I am taking the current sheet for my timesheet program. I am copying the sheet to the end of the workbook and renamming it based on the date. My problem is in VBA the original sheetname was sheet1 and everytime I add a new sheet it adds a 1 to the end.

ex.

Sheet1(3.02.01)
Sheet111(3.16.01)
Sheet1111(3.23.01)
Sheet11111(3.30.01)
Sheet111111(4.06.01)
Sheet1111111(4.13.01)
Sheet11111111(4.20.01)
Sheet111111111(4.27.01)

Get it

How do I change the sheet name in VBA

Sub NewSheet()

Dim lastdate As Date
counter = ActiveWorkbook.Sheets.Count
Sheets(counter).Copy After:=Sheets(counter)
Sheets(counter + 1).Range("L2:M2").Select
ActiveCell.Value = Sheets(counter).Range("L2") + 7
Sheets(counter + 1).Name = Month(ActiveCell.Value) & "." & Day(ActiveCell.Value) & "." & Right(Year(ActiveCell.Value), 2)

End Sub


Posted by g-man on April 23, 2001 8:39 PM

David,

The problem you have encountered relates to the 'sheet copy' method you are using.
Consider using something like the following. It will name your sheets 'sheet1',
'sheet2', 'sheet3', etc.

Sub CopySheet()
Sheets.Add
ActiveWorkbook.Sheets("Your Sheet").Cells.Copy
activesheet.Paste
ActiveWorkbook.activesheet.Select
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
End With
activesheet.Protect
End Sub

You can modify it to suit your taste. This will
also get around the 35-40 sheet copy limit imposed
by Excel97 before it flames out.

HTH
g-man

Posted by Dave Hawley on April 24, 2001 2:02 AM


Hi Dave

g-man is right!

I try to avoid copying anything when coding (unless I have to) Try this method instead. Should run a lot smoother.


Sub NewSheet()
Dim lastdate As Date
Dim SShtName As String
Dim SCellVal
Counter = Sheets.Count
SCellVal = Sheets(Counter).Range("L2") + 7
SShtName = Month(SCellVal) & "." & Day(SCellVal) & "." & Right(Year(SCellVal), 2)
Sheets.Add(After:=Sheets(Counter)).Name = SShtName
Sheets(Counter + 1).Range("L2") = SCellVal
End Sub

Dave


OzGrid Business Applications