I would like to write a macro that would create a workshee
automatically for the next year but how can i prevent it from creatin
a new worksheet everytime the macro is run.

i have work out some codes but i think it still need alot o
improvements.

i would need the program to create a worksheet for 2007 if today's yea
is 2006. which means the name of the worksheet would be next year.

Dim x As Integer
x = 2006

Workbooks("PEM_Master_Record.xls").Activate

If Year(Date) = x Then
Dim y As Worksheet
On Error Resume Next
Set y = Sheets([text(today(),"yyyy")])
If Err.Number = 0 Then Exit Sub
Application.ScreenUpdating = False
Set y = ActiveWorkbook.Worksheets.Add(after:=Sheets(Sheets .count))
y.Name = [text(today(),"yyyy")]
Application.ScreenUpdating = True
Set y = Nothing
End I

--
ReD-DevI
-----------------------------------------------------------------------
ReD-DevIL's Profile: http://www.excelforum.com/member.php...fo&userid=3262
View this thread: http://www.excelforum.com/showthread.php?threadid=52560

Hi, try this:

Sub SheetAdd()
On Error GoTo line1:
x = "2006"
Sheets(x).Select
Exit Sub
line1:
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = x
End Sub

Henrich

€žReD-DevIL" napÃ*sal (napÃ*sala):


I would like to write a macro that would create a worksheet
automatically for the next year but how can i prevent it from creating
a new worksheet everytime the macro is run.

i have work out some codes but i think it still need alot of
improvements.

i would need the program to create a worksheet for 2007 if today's year
is 2006. which means the name of the worksheet would be next year.

Dim x As Integer
x = 2006

Workbooks("PEM_Master_Record.xls").Activate

If Year(Date) = x Then
Dim y As Worksheet
On Error Resume Next
Set y = Sheets([text(today(),"yyyy")])
If Err.Number = 0 Then Exit Sub
Application.ScreenUpdating = False
Set y = ActiveWorkbook.Worksheets.Add(after:=Sheets(Sheets .count))
y.Name = [text(today(),"yyyy")]
Application.ScreenUpdating = True
Set y = Nothing
End If


--
ReD-DevIL
------------------------------------------------------------------------
ReD-DevIL's Profile: http://www.excelforum.com/member.php...o&userid=32624
View this thread: http://www.excelforum.com/showthread...hreadid=525600

Hi, thanks a lot for your help. But instead of giving "x" a fixed value
can we have a condition that once every first day of a new year it wil
automatically create a new worksheet. e.g. today is 01/01/06 then a ne
worksheet named 2007 is created, then once it is 01/01/07 a workshee
for 2008 will be added. Is that possible

--
ReD-DevI
-----------------------------------------------------------------------
ReD-DevIL's Profile: http://www.excelforum.com/member.php...fo&userid=3262
View this thread: http://www.excelforum.com/showthread.php?threadid=52560

Hi, so use this:

x = DatePart("yyyy", Date)

Henrich

€žReD-DevIL" napÃ*sal (napÃ*sala):


Hi, thanks a lot for your help. But instead of giving "x" a fixed value,
can we have a condition that once every first day of a new year it will
automatically create a new worksheet. e.g. today is 01/01/06 then a new
worksheet named 2007 is created, then once it is 01/01/07 a worksheet
for 2008 will be added. Is that possible ?


--
ReD-DevIL
------------------------------------------------------------------------
ReD-DevIL's Profile: http://www.excelforum.com/member.php...o&userid=32624
View this thread: http://www.excelforum.com/showthread...hreadid=525600

Hi, this should solve your problem:

Option Explicit
Sub SheetAdd()
Dim x As String
On Error GoTo line1:
x = DatePart("yyyy", Date)
x = x + 1
Sheets(x).Select
Exit Sub
line1:
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = x

End Sub

Henrich

€žHenrich" napÃ*sal (napÃ*sala):

Hi, so use this:

x = DatePart("yyyy", Date)

Henrich

€žReD-DevIL" napÃ*sal (napÃ*sala):


Hi, thanks a lot for your help. But instead of giving "x" a fixed value,
can we have a condition that once every first day of a new year it will
automatically create a new worksheet. e.g. today is 01/01/06 then a new
worksheet named 2007 is created, then once it is 01/01/07 a worksheet
for 2008 will be added. Is that possible ?


--
ReD-DevIL
------------------------------------------------------------------------
ReD-DevIL's Profile: http://www.excelforum.com/member.php...o&userid=32624
View this thread: http://www.excelforum.com/showthread...hreadid=525600

Thanks a lot Henrich. :)
One more question, x = DatePart("yyyy", Date)
what does DatePart stands for

--
ReD-DevI
-----------------------------------------------------------------------
ReD-DevIL's Profile: http://www.excelforum.com/member.php...fo&userid=3262
View this thread: http://www.excelforum.com/showthread.php?threadid=52560

Hi RD, please, do not hesitate and see help for further information.

Henrich

€žReD-DevIL" napÃ*sal (napÃ*sala):


Thanks a lot Henrich. :)
One more question, x = DatePart("yyyy", Date)
what does DatePart stands for ?


--
ReD-DevIL
------------------------------------------------------------------------
ReD-DevIL's Profile: http://www.excelforum.com/member.php...o&userid=32624
View this thread: http://www.excelforum.com/showthread...hreadid=525600

Oh ok. Thanks anyway :

--
ReD-DevI
-----------------------------------------------------------------------
ReD-DevIL's Profile: http://www.excelforum.com/member.php...fo&userid=3262
View this thread: http://www.excelforum.com/showthread.php?threadid=52560