ExcelBanter - View Single Post

joemeshuggah

thanks!!!

"Dave Peterson" wrote:

You can have code that writes code, but there are easier ways...

Instead of deleting the worksheet, just clear (or clearcontents) the cells.

with worksheets("A")
.cells.clearcontents 'remove values and formulas
'or
.cells.clear 'remove all the formatting, too.
'or even limit it to a range
.rows("3:99").clear 'clearcontents
'if you wanted to keep headers/footers/filters/...
End with

Or you could create a template sheet (hide it) and use that as the basis for the
new sheet. That template would have everything you need--including the event
procedures.

Dim NewSheet as worksheet

with worksheets("a-sheet-template")
.visible = xlsheetvisible
.copy _
after:=sheets(sheets.count)
set NewSheet = activesheet 'new sheet based on template
.visible = xlsheethidden
end with

'and do stuff to the NewSheet Variable
newsheet.name = "A"

====

But if you want, you can visit Chip Pearson's site:
http://www.cpearson.com/excel/vbe.aspx

joemeshuggah wrote:

i have a macro that deletes tabs a, b, c, and then re-creates them with the
current information i enter into tab d.

i have a worksheet (beforedoubleclick) procedure that i want to incorporate
into worksheet b...but the procedure is deleted each time the macro is run
because the deletion of worksheet b is part of the macro.

is there a way for the macro to write the beforedoubleclick procedure to
worksheet b after it has been deleted and re-added?

--

Dave Peterson
.