Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet and clear cells on new sheet at sametime.
I have a nice worksheet that captures input data and compares it to
expected data. After it has 5-10 inputs, I'm done with that object I'm doing work on, and have to start over again with a similar object. I want to copy that sheet that I just used to a new sheet with a command button. I could also get the copy from a sheet that has the formulas and formating but that never had any data input to it. On the new sheet I want to clear contents of the data I put in on the previous sheet. I then need a form box to pop up to ensure that my 4 starting parameters for each sheet are are checked and changed if they need to be. The people who will be filling in the spread sheets are not interested in running a macro, but might use a command button, and will probably only check for the starting parameters if they pop up like a form would do. I can get the copy form and deleteing of data with a macro, but when I add a command button, it has problems with range while its running. As for the form opening on a new sheet, my description of the cell to enter is A4:D4 with the data going into A5:D5. When I try to get it to open a form with a macro, the form pops up with 4 captions and 4 places for data, but it starts at A1. I'm racking my brain, but I'm sure I'm just manipulating it incorrectly. Help! Brad Withrow |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet and clear cells on new sheet at sametime.
Hi Brad,
If I understand you correctly, you have a worksheet that you have designed as a form for users to input data in specific cells. After you're done with it, you want to copy it, then clear the data so you can re-use it for the next project. <IMO I suggest you re-think how this gets done. For example, if you always start with the same layout then make the worksheet a template and use it for new projects as required. Give each new sheet a name that associates it with the project it's used for. Now you don't need to copy anything, nor clear any data because each project starts with its own new sheet, ready for user input. To make a worksheet a template: - Put the sheet in its own workbook. (make it the only sheet in the workbook) - Save the workbook as an ".xlt" file. To insert it in the active workbook: - Store the ".xlt" file he C:\Documents and Settings\USER_NAME\Application Data\Microsoft\Templates (This will make it available from the sheet tab Insert... dialog) - Right-click the tab of the worksheet you want to insert the new sheet BEFORE, and choose Insert... from the shortcut menu. -A new sheet based on your template is added to the workbook. - Name the sheet for the new project and start using it. The issue of prompting users to enter the required starting parameters can be handled easily by Conditional Formatting and good sheet design, so I don't see the need to have them fill in a userform when they can just fill in the respective cells on the sheet. Accomplishing this would be easier to show by example than explain it. If you're interested, I could look at your file and return it with some suggestions/examples. If so, post back and I'll send you my email info. HTH Regards, Garry |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy sheet and clear cells on new sheet at sametime.
Just a piece of additional info:
Note that the reference to USER_NAME in the folder path should contain the username of the person responsible for starting new projects. When you select ".xlt" in the File Type dropdown list, Excel will default to the current user's Documents and Settings folder. Regards, GS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Sheet to new Sheet and clear cells on original sheets | Excel Discussion (Misc queries) | |||
Copy sheet cells into differnt workbook/sheet, How? | Excel Discussion (Misc queries) | |||
copy data of two cells from Sheet 2 into one cell in Sheet 1 | Excel Worksheet Functions | |||
Copy row sheet-sheet skipping unqualified cells | Excel Worksheet Functions | |||
Macro help - copy row, clear, and paste on different sheet | Excel Programming |