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
