Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Copy hidden worksheet to new worksheet
I need some Excel 2003 macro help, please.
I have a workbook with two worksheets. The first is a roster of names and information. The second is an empty template that will use V-Lookup to retrieve information from the roster based on user inputs. I want to hide the template worksheet and create a button for the roster worksheet that has a macro attached to it. The macro needs to copy the hidden template worksheet and insert it at the end of the workbook. A user might choose to make one copy, or many. I would also like the macro to include a pop-up box in the middle of the routine where the user could enter a Tab name, and then continue on with inserting the newly titled worksheet. I am unsure of how to invoke the copying of the template worksheet, or if it is even possible to copy it while it's hidden. The users are not Excel savvy people. We don't want them to see the original. From the user's perspective, we want it to be a one-click event to create a new worksheet, with a simple pop-up to name the tab. From there, they know how to fill out the template. Can anyone give me an idea where to start with the code for invoking a copy of a worksheet, or if it's possible to invoke it while it's hidden? I hope this makes sense! Thank you in advance, Susan -- Susan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Copy hidden worksheet to new worksheet
One way:
Option Explicit Sub testme() Dim TemplWks As Worksheet Dim NewWks As Worksheet Dim NewName As String Application.ScreenUpdating = False With ThisWorkbook Set TemplWks = Worksheets("template") TemplWks.Visible = xlSheetVisible TemplWks.Copy _ befo=.Sheets(1) TemplWks.Visible = xlSheetHidden Set NewWks = .Worksheets(1) NewWks.Move _ after:=.Sheets(.Sheets.Count) End With Application.ScreenUpdating = True NewName = InputBox(Prompt:="What's the new name, Kenny?") On Error Resume Next NewWks.Name = NewName If Err.Number < 0 Then MsgBox "That name wasn't valid." & vbLf _ & "Please rename " & NewWks.Name & " yourself." Err.Clear End If On Error GoTo 0 Application.Goto reference:=NewWks.Range("A1"), Scroll:=True End Sub SueDot wrote: I need some Excel 2003 macro help, please. I have a workbook with two worksheets. The first is a roster of names and information. The second is an empty template that will use V-Lookup to retrieve information from the roster based on user inputs. I want to hide the template worksheet and create a button for the roster worksheet that has a macro attached to it. The macro needs to copy the hidden template worksheet and insert it at the end of the workbook. A user might choose to make one copy, or many. I would also like the macro to include a pop-up box in the middle of the routine where the user could enter a Tab name, and then continue on with inserting the newly titled worksheet. I am unsure of how to invoke the copying of the template worksheet, or if it is even possible to copy it while it's hidden. The users are not Excel savvy people. We don't want them to see the original. From the user's perspective, we want it to be a one-click event to create a new worksheet, with a simple pop-up to name the tab. From there, they know how to fill out the template. Can anyone give me an idea where to start with the code for invoking a copy of a worksheet, or if it's possible to invoke it while it's hidden? I hope this makes sense! Thank you in advance, Susan -- Susan -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Copy hidden worksheet to new worksheet
Create a button from the Control Toolbox. In design mode,
right click the button and select "View Code" from the pop up menu. Copy the code below into the code window. In the properties window you can put a name on the switch and set the back color, size it, etc. Private Sub CommandButton1_Click() Dim sh as Worksheet Set Tsh = Sheets("Template") 'change to actual name Tsh.Visible = True Tsh.Copy After:=Sheets(Sheets.Count) shName = InputBox("ENTER A NAME FOR A SHEET", "NEW SHEET NAME") ActiveSheet.Name = shName Tsh.Visible = False End Sub "SueDot" wrote in message ... I need some Excel 2003 macro help, please. I have a workbook with two worksheets. The first is a roster of names and information. The second is an empty template that will use V-Lookup to retrieve information from the roster based on user inputs. I want to hide the template worksheet and create a button for the roster worksheet that has a macro attached to it. The macro needs to copy the hidden template worksheet and insert it at the end of the workbook. A user might choose to make one copy, or many. I would also like the macro to include a pop-up box in the middle of the routine where the user could enter a Tab name, and then continue on with inserting the newly titled worksheet. I am unsure of how to invoke the copying of the template worksheet, or if it is even possible to copy it while it's hidden. The users are not Excel savvy people. We don't want them to see the original. From the user's perspective, we want it to be a one-click event to create a new worksheet, with a simple pop-up to name the tab. From there, they know how to fill out the template. Can anyone give me an idea where to start with the code for invoking a copy of a worksheet, or if it's possible to invoke it while it's hidden? I hope this makes sense! Thank you in advance, Susan -- Susan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Copy hidden worksheet to new worksheet
Forgot to change the Dim statement. Use this code instead.
Private Sub CommandButton1_Click() Dim Tsh as Worksheet Set Tsh = Sheets("Template") 'change to actual name Tsh.Visible = True Tsh.Copy After:=Sheets(Sheets.Count) shName = InputBox("ENTER A NAME FOR A SHEET", "NEW SHEET NAME") ActiveSheet.Name = shName Tsh.Visible = False End Sub "SueDot" wrote in message ... I need some Excel 2003 macro help, please. I have a workbook with two worksheets. The first is a roster of names and information. The second is an empty template that will use V-Lookup to retrieve information from the roster based on user inputs. I want to hide the template worksheet and create a button for the roster worksheet that has a macro attached to it. The macro needs to copy the hidden template worksheet and insert it at the end of the workbook. A user might choose to make one copy, or many. I would also like the macro to include a pop-up box in the middle of the routine where the user could enter a Tab name, and then continue on with inserting the newly titled worksheet. I am unsure of how to invoke the copying of the template worksheet, or if it is even possible to copy it while it's hidden. The users are not Excel savvy people. We don't want them to see the original. From the user's perspective, we want it to be a one-click event to create a new worksheet, with a simple pop-up to name the tab. From there, they know how to fill out the template. Can anyone give me an idea where to start with the code for invoking a copy of a worksheet, or if it's possible to invoke it while it's hidden? I hope this makes sense! Thank you in advance, Susan -- Susan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Copy hidden worksheet to new worksheet
Thanks to both of you - you were of great help!!! :)
-- Susan "SueDot" wrote: I need some Excel 2003 macro help, please. I have a workbook with two worksheets. The first is a roster of names and information. The second is an empty template that will use V-Lookup to retrieve information from the roster based on user inputs. I want to hide the template worksheet and create a button for the roster worksheet that has a macro attached to it. The macro needs to copy the hidden template worksheet and insert it at the end of the workbook. A user might choose to make one copy, or many. I would also like the macro to include a pop-up box in the middle of the routine where the user could enter a Tab name, and then continue on with inserting the newly titled worksheet. I am unsure of how to invoke the copying of the template worksheet, or if it is even possible to copy it while it's hidden. The users are not Excel savvy people. We don't want them to see the original. From the user's perspective, we want it to be a one-click event to create a new worksheet, with a simple pop-up to name the tab. From there, they know how to fill out the template. Can anyone give me an idea where to start with the code for invoking a copy of a worksheet, or if it's possible to invoke it while it's hidden? I hope this makes sense! Thank you in advance, Susan -- Susan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy hidden worksheet | Excel Programming | |||
Copy Worksheet and Exclude Hidden fields | Excel Worksheet Functions | |||
How do I copy a worksheet so that hidden columns remain secret? | Excel Discussion (Misc queries) | |||
How do I NOT copy hidden rows to a new worksheet? | Excel Discussion (Misc queries) | |||
How? Macro to copy range to new worksheet, name new worksheet, loop | Excel Programming |