EXCEL 2007 - need help creating a button that displays the contents of the active sheet and inserts it into a new worksheet

Question

I have a search all over the site to find the answer to my problem, and most related solutions for a much more complex problem. Here is what I need to do. I created a simple form in Excel 2007. I am looking for an opportunity to add a button at the bottom of the form that allows the user to click on the button and copy this worksheet to a new sheet in the same excel document. Basically just duplicates the active worksheet.

I tried to do this using macros, but did not get the desired results, and most of our employees still use Excel 2003, so I'm not sure whether macros will work in the old version of excel. I do not know any VBA, so I come here looking for help from all of you.

So, to repeat.

• One sheet of an Excel document with a simple form and a command button at the bottom of the active worksheet
• Copy and paste command button this worksheet to a new worksheet in the same excel document
• A solution that could work both in Excel 2003 and in 2007, if possible. If not, then in 2007.

Thank you for helping Novice Excel.

Answer 1

Assuming you know how to add a button here, this is a simple line of code to duplicate an active sheet:

Sub Button1_Click() ActiveSheet.Copy after:=ActiveSheet End Sub 

Answer 2

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

 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 

Answer 3

You should find that 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 unload a copy of the worksheet between the current sheet and the next. I tested it in Excel 2003 and Excel 2007, and it works in both. This does not give the second a beautiful name sadly - it just gets the same name as the original sheet with (2) after it.

All formatting, protection, and formulas are also copied - this is a copy of the first.

Answer 4

I know that the question is quite old, but just wanted to notice that you (and the user) can do the same with the null code: right-click on the sheet name at the bottom and select "Move" or "Copy" .., then set Create a copy box and click OK. Yes, it takes 4 clicks, but it is very simple and avoids the code.