Move a sheet
 

Hello NG,
I have this macro I run on the first of the year and it works OK except for
the
Sheets(CurrentYear).Move After:=Sheets(WS_Count)
It doesn't seem to see or recognize this line, no errors or anything, just
skips over it.
Any suggestion on how I can get the new sheet to move to the last position?

Thanks
James

Dim CurrentYear As String
Dim LastYear As String
Dim WS_Count As Integer
CurrentYear = Right(Date, 4) 'get just the year from the current date


WS_Count = Worksheets.Count

Sheets.Add
Sheets("Sheet1").Name = CurrentYear
Sheets(CurrentYear).Select
Sheets(CurrentYear).Move After:=Sheets(WS_Count)
Sheets(CurrentYear).Select

Move a sheet
 

James,

I would do it in the Add method like so....

Sub AddYearSheetToEnd()
Dim ws As Worksheet
Dim CurrentYear As String

CurrentYear = Right(Date, 4)

Set ws = Worksheets.Add(After:=Sheets(Sheets.Count))
ws.Name = CurrentYear

End Sub



--
Charles
www.officezealot.com


"James Agostinho" wrote in message
k.net...
Hello NG,
I have this macro I run on the first of the year and it works OK except
for
the
Sheets(CurrentYear).Move After:=Sheets(WS_Count)
It doesn't seem to see or recognize this line, no errors or anything, just
skips over it.
Any suggestion on how I can get the new sheet to move to the last
position?

Thanks
James

Dim CurrentYear As String
Dim LastYear As String
Dim WS_Count As Integer
CurrentYear = Right(Date, 4) 'get just the year from the current
date


WS_Count = Worksheets.Count

Sheets.Add
Sheets("Sheet1").Name = CurrentYear
Sheets(CurrentYear).Select
Sheets(CurrentYear).Move After:=Sheets(WS_Count)
Sheets(CurrentYear).Select

Move a sheet
 

James,

A couple of things:

I'm not sure you can assume the new sheet will be named Sheet1, anyways you
don't need to assume so.
It might be simplet to do without the WS_count variable if you only use it
once.
You don't need to select the new sheet - it becomes the active sheet after
it's created.
I'd refer only to either sheets or worksheets (sheets includes chart sheets
if there are some).

This is how I'd write it:

Sub test()

Dim CurrentYear As String
Dim LastYear As String

CurrentYear = Right(Date, 4) 'get just the year from the current date

Sheets.Add
With ActiveSheet
.Name = CurrentYear
.Move After:=Sheets(Sheets.Count)
End With

End Sub

hth,

Doug

"James Agostinho" wrote in message
k.net...
Hello NG,
I have this macro I run on the first of the year and it works OK except
for
the
Sheets(CurrentYear).Move After:=Sheets(WS_Count)
It doesn't seem to see or recognize this line, no errors or anything, just
skips over it.
Any suggestion on how I can get the new sheet to move to the last
position?

Thanks
James

Dim CurrentYear As String
Dim LastYear As String
Dim WS_Count As Integer
CurrentYear = Right(Date, 4) 'get just the year from the current
date


WS_Count = Worksheets.Count

Sheets.Add
Sheets("Sheet1").Name = CurrentYear
Sheets(CurrentYear).Select
Sheets(CurrentYear).Move After:=Sheets(WS_Count)
Sheets(CurrentYear).Select