Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy sheet
Hi, I have a time sheet (only one sheet in the workbook), at end of
each 4 week period users clicks new month - this clears sheet, puts new dates in and carries forward current hours (credit, debit). I want to be able the workbook to save a copy to a new sheet (within same workbook), then clear times etc in main sheet (Sheet1 only). I have the following which works but want to rename the new sheet (Sheet1 (2) ) to Period 1, then next month period 2 etc. Thanks Sub CopySheet() Sheets("Sheet1").Select Sheets("Sheet1").Copy After:=Sheets(2) Sheets("Sheet1 (2)").Select ActiveWorkbook.Sheets("Sheet1 (2)").Tab.ColorIndex = 35 Sheets("Sheet1").Select End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy sheet
You can try this, it works for Period 1, but how can we get information on
the current period No? I'll try to help, if you give some more details! Sub CopySheet() Sheets("Sheet1").Select Sheets("Sheet1").Copy After:=Sheets(2) Sheets("Sheet1 (2)").Select Activesheet.Name ="Period 1" Activesheet.Tab.ColorIndex = 35 Sheets("Sheet1").Select End Sub Regards, Stefi Paul ezt Ã*rta: Hi, I have a time sheet (only one sheet in the workbook), at end of each 4 week period users clicks new month - this clears sheet, puts new dates in and carries forward current hours (credit, debit). I want to be able the workbook to save a copy to a new sheet (within same workbook), then clear times etc in main sheet (Sheet1 only). I have the following which works but want to rename the new sheet (Sheet1 (2) ) to Period 1, then next month period 2 etc. Thanks Sub CopySheet() Sheets("Sheet1").Select Sheets("Sheet1").Copy After:=Sheets(2) Sheets("Sheet1 (2)").Select ActiveWorkbook.Sheets("Sheet1 (2)").Tab.ColorIndex = 35 Sheets("Sheet1").Select End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy sheet
Try code like the following:
Sub AAA() Dim CurrWS As Worksheet Dim NewWS As Workbook Dim N As Long Dim M As Long Dim CurrName As String Dim NewName As String Set CurrWS = ActiveSheet CurrName = CurrWS.Name N = InStrRev(CurrName, " ") If N = 0 Then Exit Sub End If M = CLng(Mid(CurrName, N + 1)) M = M + 1 NewName = Left(CurrName, N) & CStr(M) CurrWS.Copy after:=Worksheets(CurrName) ActiveSheet.Name = NewName End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Tue, 10 Mar 2009 01:03:51 -0700 (PDT), Paul wrote: Hi, I have a time sheet (only one sheet in the workbook), at end of each 4 week period users clicks new month - this clears sheet, puts new dates in and carries forward current hours (credit, debit). I want to be able the workbook to save a copy to a new sheet (within same workbook), then clear times etc in main sheet (Sheet1 only). I have the following which works but want to rename the new sheet (Sheet1 (2) ) to Period 1, then next month period 2 etc. Thanks Sub CopySheet() Sheets("Sheet1").Select Sheets("Sheet1").Copy After:=Sheets(2) Sheets("Sheet1 (2)").Select ActiveWorkbook.Sheets("Sheet1 (2)").Tab.ColorIndex = 35 Sheets("Sheet1").Select End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy sheet
On 10 Mar., 16:35, Chip Pearson wrote:
Try code like the following: Sub AAA() * * Dim CurrWS As Worksheet * * Dim NewWS As Workbook * * Dim N As Long * * Dim M As Long * * Dim CurrName As String * * Dim NewName As String * * Set CurrWS = ActiveSheet * * CurrName = CurrWS.Name * * N = InStrRev(CurrName, " ") * * If N = 0 Then * * * * Exit Sub * * End If * * M = CLng(Mid(CurrName, N + 1)) * * M = M + 1 * * NewName = Left(CurrName, N) & CStr(M) * * CurrWS.Copy after:=Worksheets(CurrName) * * ActiveSheet.Name = NewName End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional * * Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLCwww.cpearson.com (email on web site) On Tue, 10 Mar 2009 01:03:51 -0700 (PDT), Paul wrote: Hi, I have a timesheet(only onesheetin the workbook), at end of each 4 week period users clicksnewmonth - this clearssheet, puts newdates in and carries forward current hours (credit, debit). I want to be able the workbook tosavea copy to anewsheet(within same workbook), then clear times etc in mainsheet(Sheet1 only). I have the following which works but want to rename thenewsheet (Sheet1 (2) ) to Period 1, then next month period 2 etc. Thanks Sub CopySheet() * *Sheets("Sheet1").Select * *Sheets("Sheet1").Copy After:=Sheets(2) * *Sheets("Sheet1 (2)").Select * *ActiveWorkbook.Sheets("Sheet1 (2)").Tab.ColorIndex = 35 * *Sheets("Sheet1").Select End Sub- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - This code looks very nice... It seems that the sheets generated will be named current sheet +1? Is there any way to name it after the sheet to the utmost right+1? E.g. if the rightmost sheet is named 36, the nextsheet will be named 37? This macro if used on sheet 36 it wouldn't be a problem... But what if I use the macro from a Main sheet(e.g. 1) and wanted to generate a new sheet each time with an increasing number, any suggetions for this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Copy/autofill Text from sheet to sheet if meets criteria | Excel Discussion (Misc queries) | |||
How can i copy data from a tabbed working sheet to a summary sheet | Excel Discussion (Misc queries) | |||
how to copy a cell with formula from sheet 1 (data is all vertical) into sheet 2 | Excel Worksheet Functions | |||
'Copy to' Advance Filter depend only on sheet ID not start sheet | Excel Worksheet Functions | |||
relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) |