Copy sheet and clear cells on new sheet at sametime.

[Brad Withrow]

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 A44 with the data going into A55. 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

 

[GS]

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 here:

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

 

[GS]

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