views:

93

answers:

3

Hello! I have search throughout this site to find a answer to my problem and most of the related solutions are for a far more complicated problem. Here is what I need to have done. I created a simple form in Excel 2007. I am looking for the ability to add a button at the bottom of the form which allows the user to click on the button and copy that worksheet into a new worksheet within the same excel document. Basically just duplicating the active worksheet.

I tried to do it with macros but did not get the desired results, and most of our co-workers still use Excel 2003 so I am not sure if macros will work in the older version of excel. I do not know any VBA which is why I come here in search of help from you all.

So to recap.

  1. One sheet Excel document with a simple form and a command button at the bottom of the active worksheet
  2. The command button "Copy and Paste" that worksheet into a new worksheet within the same excel document
  3. A solution that could work in both Excel 2003 and 2007 if possible. If not, for 2007.

Thanks so much ahead of time for anyone who is willing to help out a Novice Excel User.

A: 

Maybe something like this (tested in Excel 2003 only):

Dim srcSheet, dstSheet
    Set srcSheet = ActiveSheet

    Sheets.Add
    Set dstSheet = ActiveSheet

    srcSheet.Activate
    srcSheet.Cells.Select
    Selection.Copy

    dstSheet.Activate
    dstSheet.Cells.Select
    ActiveSheet.Paste
Oleg
Ok I just tried this using excel 2007 and got the following error: Run-time error '9': Subscript out of range.
Stat1124
I used another name. here is the code: Private Sub CommandButton1_Click()Sheets("MultipleChoice").SelectSelection.CopySheets.AddSheets("NewSheet").SelectActiveSheet.PasteEnd Sub
Stat1124
So my question now is should the name in " " be any name I choose or does one have to be specific from the source worksheet that is being copied!
Stat1124
Hmmm.. When I set the Sheets name to be the same as the worksheet for both of them, it copies a blank worksheet to the beginning of the document. If I change either name to something else, I get the same Run-time error stated above.
Stat1124
I have updated my answer. Please check how it works.
Oleg
Beautiful! It works! Only problem is that it doesn't hold any of the formatting and most importantly the worksheet protection so that the user may only type in the unlocked cells to preserve the formatting. Is this possible or is that what must be sacrificed to copy a worksheet?
Stat1124
To say the truth - I don't know answer to this question.
Oleg
You have been very helpful thus far. I appreciate your time!!
Stat1124
+1  A: 

Assuming that you know how to add a button here is a simple line of code to duplicate the active worksheet:

Sub Button1_Click()
    ActiveSheet.Copy after:=ActiveSheet
End Sub
Andrew
Andrew my man!! Brilliant!! That is perfect and it held on to all the formatting and worksheet protection! Thanks so much!
Stat1124
nice appreciation, if only everyone was so appreciative.
Anonymous Type
A: 

You should find this method will work in both Excel 2003 and Excel 2007. In your form, add the following method:

Sub CopySheet(WorkSheetName as String)

    Dim WrkSht As Worksheet
    Set WrkSht = Sheets(WorkSheetName)

    WrkSht.Copy After:=Sheets(WorkSheetName)

    Set WrkSht = Nothing

End Sub

From the button click event, call it using:

Sub Button1_Click()

    Call CopySheet("WorkSheetToCopyName") 
    'You could also replace the string name with ActiveSheet if you so wish

End Sub

This will dump a copy of the worksheet in between the current sheet and the next one. I've tested it in Excel 2003 and Excel 2007 and it works in both. It doesn't give the second one a pretty name sadly - it just gets the same name as the source worksheet with (2) put after it.

All the formatting, protection and formulas are copied across too - it's a carbon copy of the first.

BenAlabaster
Ben, I will give this a try and send it over to the people with Excel 2003 to make sure they can view it, along with Andrew's suggestion. I appreciate you taking the time to help me out!
Stat1124