Using Excel Function vs. VBA

[dipitiduda2]

Problem:
The worksheet is comprised of Sheet1 that has calculated
fields and macro buttons that: a) add rows to the sheet;
and b) should create a copy of Sheet1 properties with the
calculated fields (no data, just formulas) to Sheet2.

Is there an Excel function that will copy the properties
and calculated fields to Sheet2? Or, do I need to create
the function in VB? If so, what command could I use? I
also need to modify the column header names, etc. upon
copying to Sheet2.

 

[Loomah]

Hi
I suspect you'll have to go down the code route for this one. I'm not aware of any worksheet function that will copy information in the way you are suggesting you want.

But first I would suggest reposting to the VBA forum and add a little more info to your post. The first thing I'd like to know is what you mean by properties? The reason I suggest this is that I may not have the opportunity to follow this up!!

That said this is one method that you may start looking at. It copies the first sheet (Original) and names it Duplicate after deleting the existing duplicate. This means that you have everyting in your second sheet exactly as it is in the first. It then removes any data from duplicate - leaving calculations and TEXT row/column headings.

Sub b()
On Error Resume Next 'allow for Duplicate not existing!!
Application.DisplayAlerts = False
    Worksheets("Duplicate").Delete
    Worksheets("Original").Copy after:=Worksheets("original")
    With ActiveSheet
        .Name = "Duplicate"
        .UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
    End With
Application.DisplayAlerts = True
End Sub

As for the column headers work along the lines of

Range("A1")="Duplicate Heading"

Hope this is of some help

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[SkipVought]

Do you already have stuff on sheet2 before this copy needs to take place? Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[dipitiduda2]

User Loomah asks: What do you mean by properties? My response: Sheet1 formatting (properties) should match Sheet2 formatting exactly except for the column headings and some protected text in Sheet2. Thanks for the information on posting to VBA forum and the code for the subroutine.

My response to SkipVought: No, Sheet2 does not exist (hence, no data) until the user clicks on the macro button from Sheet1 that creates a duplicate sheet (Sheet2).

 

[SkipVought]

Why not just copy the sheet? Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Loomah]

....like in my code!
Sometimes I forget you can do things manually too!!!!
D'Oh
;-) If a man says something and there are no women there to hear him, is he still wrong?