Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to move rows from one sheet to another sheet (macro) | Excel Discussion (Misc queries) | |||
Creating form on sheet 1 and having info move to sheet 2 | Excel Discussion (Misc queries) | |||
How do I move daily data from one sheet to a 'yearly sheet' | Excel Worksheet Functions | |||
Move data to new sheet - rename sheet based on criteria ? | Excel Discussion (Misc queries) | |||
move rows of data seperated in a sheet to a sheet with no separat | Excel Worksheet Functions |