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
0
burnspaul (2)
3/10/2009 1:30:15 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
677 Views

Similar Articles

[PageSpeed] 34

Hi

Try this:

Sub CopySheet()
    If Sheets(Sheets.Count).Name Like "Period*" Then
        Period = Mid(Sheets(Sheets.Count).Name, 7)
        Period = Period + 1
    Else
        Period = 1
    End If
    Sheets("Sheet1").Copy After:=Sheets(Sheets.Count)
    Sheets("Sheet1 (2)").Select
    ActiveWorkbook.Sheets("Sheet1 (2)").Tab.ColorIndex = 35
    Sheets(Sheets.Count).Name = "Period" & Period
    Sheets("Sheet1").Select
End Sub

Regards,
Per

"Paul" <burnspaul@hotmail.co.uk> skrev i meddelelsen 
news:011c8f68-0a26-45df-b2ff-7ee952234d19@h20g2000yqn.googlegroups.com...
> 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 

0
per.jessen (135)
3/10/2009 2:01:07 PM
Hi,

I'm not sure where you want to put the sheet because if your workbook only 
has one sheet I don't see how it can add the next sheet after sheet(2).  
However, lets assume you want to add the new sheet directly after the sheet 
you are on.

Sub CopySheet()
    ActiveSheet.Copy After:=ActiveSheet
    ActiveSheet.Tab.ColorIndex = 35
    ActiveSheet.Name = "Period" & Sheets.Count - 1
End Sub

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"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
> 
0
3/10/2009 3:01:35 PM
On Mar 10, 2:01=A0pm, "Per Jessen" <per.jes...@mail.dk> wrote:
> Hi
>
> Try this:
>
> Sub CopySheet()
> =A0 =A0 If Sheets(Sheets.Count).Name Like "Period*" Then
> =A0 =A0 =A0 =A0 Period =3D Mid(Sheets(Sheets.Count).Name, 7)
> =A0 =A0 =A0 =A0 Period =3D Period + 1
> =A0 =A0 Else
> =A0 =A0 =A0 =A0 Period =3D 1
> =A0 =A0 End If
> =A0 =A0 Sheets("Sheet1").Copy After:=3DSheets(Sheets.Count)
> =A0 =A0 Sheets("Sheet1 (2)").Select
> =A0 =A0 ActiveWorkbook.Sheets("Sheet1 (2)").Tab.ColorIndex =3D 35
> =A0 =A0 Sheets(Sheets.Count).Name =3D "Period" & Period
> =A0 =A0 Sheets("Sheet1").Select
> End Sub
>
> Regards,
> Per
>
> "Paul" <burnsp...@hotmail.co.uk> skrev i meddelelsennews:011c8f68-0a26-45=
df-b2ff-7ee952234d19@h20g2000yqn.googlegroups.com...
>
>
>
> > 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()
> > =A0 =A0Sheets("Sheet1").Select
> > =A0 =A0Sheets("Sheet1").Copy After:=3DSheets(2)
> > =A0 =A0Sheets("Sheet1 (2)").Select
> > =A0 =A0ActiveWorkbook.Sheets("Sheet1 (2)").Tab.ColorIndex =3D 35
> > =A0 =A0Sheets("Sheet1").Select
> > End Sub- Hide quoted text -
>
> - Show quoted text -

Thank you very much, works well.
0
burnspaul (2)
3/10/2009 3:08:28 PM
Reply:

Similar Artilces:

How can I copy pages between two Publisher files?
I have a fairly large Publisher file (200+) pages. As this document is being developed I want to send new/edited pages to others for proofing. I cancopy the file then delete everything I don't want to send, but that is pretty tedious. Also, I would like to insert/replace pages from the proofreaders into mymain document without having to move an object at a time. TIA cvholme <cvholme@discussions.microsoft.com> was very recently heard to utter: > I have a fairly large Publisher file (200+) pages. As this document > is being developed I want to send new/edited pages to o...

print page seprator for mutiple copies in excel is not working
Hi am using print page seprator for printing in that when i give print thru Excel 2003 normaly it is wokring if i give as muliple copies to print then for each and every copy a seprator page is printing. Other than excel if i give thru word or powerpoint it is working fine i.e only one seprator page will print for muliple copies, Even i tried with different seprator page file still i have this problem Need some solution please ...

Adding data from another sheet.
I hope I can find some help on this one. I have a spreadsheet that consists of two sheets. The first is a form. The second is a list of questions. I have added a checkbox next to each question on sheet 2 to select the question and add it to the form on sheet 1. The problem: when you select a question on sheet 2 it will only add it to a specific cell on sheet 1. So if I select question 15 it will go in a position like it is the 15th question when actually I would like it to be the first question. The question: Is there a way to make the questions start adding to the top cell on sheet 1...

Formula auditing on protected sheets
Simple question: is there a easy (or difficult?) way to track precedent and dependent cells on sheets which are protected? Thanks, Andrew ...

comparing values on different sheets and deleting
Is there a way delete value on sheet2 from the values found on sheet1? For example I have a list of items on sheet1. I need to delete all values from sheet1 that match on sheet2. thank. np You might have to do this with a few steps. Add a column in list #1 such as: =VLOOKUP(A2,Sheet2!$A$1:$A$5,1,FALSE) Assuming A2 is the item you are looking for… Assuming the names that might have dups in them is A1:A5 on Sheet2 Anything *not* found will be listed as N/A which mean Not Available. Step 2, turn on Auto Filter. Data Filter Autofilter in 2003 or Home Tab Sort and Filter...

copying or downloading clip art
Why can't I copy or download clip art? When I select an item my selection basket stays empty. ...

Rule to move a Message is Copying instead
I have built many rules before... I have a rule that when a message comes in containing specific words in the subject it is supposed to MOVE the message to a specific folder and play a Sound. Instead it COPIES the Message and plays a sound, so I end up with duplicates. I checked and rechecked and I have selected MOVE and nothing else. I am running Outlook 2007 Sp2 standalone (no server) Bob Sisson www.inspectionsbybob.com A move is actually copy + delete, perhaps the "play sound" is interfering with the "delete", try removing the "play soun...

Why does the font change when copying between Excel workbooks
I receive workbooks from colleagues for collation into a single workbook. When I try to copy data between workbooks, it is not pasted with the font properties of the original data or the default properties of the receiving workbook, even if those fonts are identical, but in some font with an uninteligible oriental character name. Can anybody tell me why this is? You don't have the same fonts as your colleagues on your local machine. "Paul Newbery CSC" wrote: > I receive workbooks from colleagues for collation into a single workbook. > When I try to copy data betwee...

Copying formatting
Hi. If I had the value '5' in cell A1, I could put that in cell A2 by typing '=A1' in cell A2. Then if I change cell A1, A2 changes automatically. I want to be able to do the same thing with formatting rather than values (especially background colour, border and so on). Is there a way? Richard Take a look at conditional formatting Robert Flanagan http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Richard Cavell" <richardcavell@mail.com> wrote in message news:d0d62f41-7627-44f6-b5bf-b11bb9c58db0@35g2000pry.googl...

How do you copy contacts into an email?
Hello all. Outlook 2000 with all necessary service packs and updates. I want to be able to copy a list of 30 contacts into the body of an email. I have to send emails every now and then with the names of these individuals listed in the body of the email. I already know how to export contacts into an excel spreadsheet. However, Outlook 2k does not give you the choice to export into Word. When I export into excel and I copy more than one name at a time they show up in the email with the gridlines wrapped around the names from the excel spreadsheet. I have removed the gridlines from the...

Excel: Copying A Formula
How do I copy this formula ='Daily Takings'!C29-B7-D7-E7 from a cell in one workbook to a cell in another totaly unrelated workbook without the formula altering to accomatate its new position and without it relating back to its old workbook. At the moment the formula when moved adds the title of the old workbook at the start of the formula so that it ends up looking like this:- ='[NEW A 10th Amended Accounts Cental Cafe 19.7.09.to18.07]Daily Takings'!C29-B7-D7-E7 I have put the $ sign before the letters/numbers like so ='Daily Takings'!$C$29-$B$7-...

Pink Sheet stocks
I am using MS Money 2005 Premium, Version 14.0.125.1105 and have recently had a need for Portfolio Manager to list and update the price on a stock that slipped into the Pink Sheets. I can not get Money to recognize this stock symbol, although it is valid in Yahoo. What do I need to do, or does Money not stoop to that level? This stock ABRXQ.PK is one I made the bad judjement of keeping when it fell from grace. It opened this morning in the Pink Sheets. This was ABRX a few days ago, then it was delisted from the Nas and became ABRXQ. This morning it was handed off to the pink sheets and acquir...

copy fields forward for new records.
I am trying to copy fields forward in a data entry form, so the user doesn't have to keep re-entering certain fields that usually stay the same. Anybody know how to do this? Thanks! Hi Open the form in design view and set the Default value of the control (right click the control and open the properties box to do this) Hope this helps -- Wayne Manchester, England. "ColbyS" wrote: > I am trying to copy fields forward in a data entry form, so the user doesn't > have to keep re-entering certain fields that usually stay the same. Anybody > know how to do...

How to delete all blank rows in a sheet?
Hello! When I paste or import text from a CSV into an Excel sheet and then edit it, I often end up with dozens of blank rows in the middle of sections of populated rows. Is there an easy way to find and delete all blank rows in a spreadsheet or range of rows? The only way to do it right now is a very time consuming Ctrl-click to highlight a bunch and then Edit, Delete them. Thanks for helping! Gregg Hill If there's a column in your data that is always populated if there's data in the row, select the column, choose Edit/Goto/Special, check the "blank" checkbox, then cl...

Active sheet problem / macro button
Hi , I want to be able to run the code below from any sheet and also with a macro button. I want to place the macro button on the first sheet [financial summary]. but the macro filter would be activated on a different sheet [production_schedule} Sub CopyFilter() Application.ScreenUpdating = False Dim rng As Range Dim rng2 As Range If Not ActiveSheet.AutoFilterMode Then ActiveSheet.Range("F3").AutoFilter End If ActiveSheet.Range("$A$4:$IK$3277").AutoFilter Field:=6, Criteria1:= _ "HR & Payroll" With ActiveSheet.AutoFilter...

Leave a copy on server
I have a new Exchange server with OST, as well as some POP accounts that use a PST. I can easily tell the POP accounts to leave the mail on the server but have found no way to tell the Exchange account the same. How can I mix this? I want to receive the POP accounts into the PST inbox. I run Outlook on multiple computers so I have backup copies of those messages in case of hardware failure (or theft). I have added an Exchange account (since the company now has Exchange), and enabled the OST file to keep a local copy. I'd much prefer to leave the Exchange inbox messages on the server (...

Copying Protected Worksheets
I have an excel spreadsheet that was password protected by an unknown user. Its an excellent tool and I'd like to adapt it for personal use. I've tried copying it to a blank spreadsheet and also "saving it as" another name in another folder. Neither method will allow me to make adjustments. There are numerous calculations I'd spend forever trying to create from scratch. Is there a way to do what I want? This works well: http://www.erlandsendata.no/english/downloads/tools.php --- Message posted from http://www.ExcelForum.com/ Or try this: Code: ---------------...

Copy and Paste text to Word
I have a large spreadsheet of data. There are no values or formulas. I need to cut and past this into word in a way that shows the paste only as text - normally it has an outline of the cells. Reason is that I need to save this as a .txt file for an upload to another system. Thanks in advance (again) > way that shows the paste only as text In Word, try: Edit > Paste special > Unformatted Text -- Rgds Max xl 97 --- Please respond, in newsgroup xdemechanik <at>yahoo<dot>com ---- "AndrewS" <anonymous@discussions.microsoft.com> >I have a large spr...

Combo Boxes getting data from Work Sheet.
I have a User Form that has a combo Box on it. Is it possible for the Combo Box to get it's Data from a Work Sheet in the same Work Book as the User Form instead of doing in Code as follows: With Me.Engineer_2 .AddItem "1" .AddItem "2" .AddItem "3" .AddItem "4" .AddItem "5" .AddItem "6" End With The reason I ask is because I would like to be able to set it up as follows: Combo Box 1 = Customer 1 Combo Box 2 = State Combo Box 3 = Site ID User Picks the Cust...

how can i relate between two columns in two sheets?
Question no too clear - you can use this space to give a detailed question. But here goes: On Sheet1 I can type formulas such as =Sheet2!A1 =SUM(Sheet2!A:A10) If I type = and then click on the cell A1 of a worksheet called My Yearly Totals, I will get the formula ='My Yearly Totals'!A1 Note that a sheet name having spaces get surrounded by single quotes. Does this answer your question? best wishes -- Bernard Liengme Microsoft Excel MVP people.stfx.ca/bliengme email address: remove uppercase characters REMEMBER: Microsoft is closing the newsgroups; We will all me...

link to specific sheet in excel file which contains points and spaces
I am trying to make a link in Acces to an Excel file. This excel file contains several sheet with different names. My form in Access has a field that corresponds to the name of the excel sheets (Formname in script). When the name in the form contains points or spaces my link to the sheet with the same name does not work. I tried to put brackets [] on either side of the name, but then linking does not work at all. Please let me know if you have any suggestions. Code (works when Formname.Value does not contain spaces or points etc): Private Sub CommandActionRegister3_Click() Dim Locatio...

Write formula for simple copy and paste to another cell
I have a worksheet that contains hundreds of columns. Each group of three are related to each other (Cols A-C, D-F, etc...). I have this formula that works well for columns A-C =IF(NOT(ISBLANK('Page 5 Counts'!B2)),VLOOKUP('Page 5 Counts'!B2,'Color Key'!$A$1:$B$87,2,FALSE),IF(AND(COLUMN()=COLUMN($B$1),ROW()=1),IF($A$2=$A$1,"",1),IF(AND(COLUMN()=COLUMN($C$1),ROW()=1),$B$1,IF(AND(COLUMN()=COLUMN($B$2),ROW()=2),IF($A$2=$A$1,2,1),IF(AND(COLUMN()=COLUMN($C$2),ROW()=2),IF($A$2=$A$3,"",IF($A$2=$A$1,$B$2,1)),IF(AND(COLUMN()=COLUMN($B$3),ROW()=3),IF($A...

Links to other sheets only work if the sheet is opened
When I link cels to another excel spreadsheet the links stay alive as long as the othe sheet is open. If not opened the cell consisting the link show #VALUE! How come ? Hi what is your exact formula -- Regards Frank Kabel Frankfurt, Germany Arnold de Lange wrote: > When I link cels to another excel spreadsheet the links stay alive as > long as the othe sheet is open. If not opened the cell consisting the > link show #VALUE! > How come ? Hi, Unlike Lotus, Excel only likes links when files are open. When creating the link, have source file open and select the cell to lin...

copy between worksheets
Hello, I want to copy part of workskeet to another worksheet, but after i do copy and paste, the format of the worksheet is changed. For Example, I want to copy a cell area (A3 to C10) to another sheet. And it contains images and text. After i paste to another worksheet, the position of images are changed. Also the cell width and format are changed too. How can i do copyand paste a celll area contains images into antoher worksheet with original format and position? Thank you Mooscar -- mooscar ------------------------------------------------------------------------ mooscar's Profile...

Copying a worksheet and retaining values (not formulas)
I have a comprehensive sales spreadsheet setup which reads raw data from my PC, collates and calculates, and displays a summary sheet. This summary sheet is fine as long as it is on my PC, but sending it to others who do not share the source data means all the links are broken. Rather than give access to the links, I want to create a file containing the actual calculated values, layout and formatting, for sending to others. Can this be done? (and if so, how!) Hi 1. way Right-click on sheet tab, select 'Move or Copy', check 'Create a Copy' and select 'New book'...