vba: Copy worksheet including named ranges not including macros

[urungus]

Is there a way to programatically copy a worksheet from one workbook to another in Excel 2003 in the following way, without having to activate or install any additional code modules (eg VBA Extensibililty library):

copy values, formats, named ranges
do not copy macros or buttons embedded in the source sheet

If I use vbas worksheet.copy function, it includes the macros, which are hard to get rid of

If I do a worksheet.cells.copy, it doesn't copy the named ranges.

Thanks for any advice.

 

[Datsmart]

I think the easiest solution would be to get rid of the WorkSheet code after the copy.

This code deletes all code from the active worksheet module.

Sub DeleteSheetModuleCode()
Dim x As Integer
Dim Proceed As VbMsgBoxResult
Dim Prompt As String
Dim Title As String

Prompt = "Are you certain that you want to delete" & vbCrLf _
& "all Code from " & ActiveSheet.Name & " Module?"
Title = "Verify Procedure"

Proceed = MsgBox(Prompt, vbYesNo + vbQuestion, Title)
If Proceed = vbNo Then
MsgBox "Procedure Canceled", vbInformation, "Procedure Aborted"
Exit Sub
End If

On Error Resume Next

With ActiveWorkbook.VBProject
    For x = .VBComponents.Count To 1 Step -1
    If Not .VBComponents(x).CodeModule.Name = "Sheet2" Then
        .VBComponents(x).CodeModule.DeleteLines _
        1, .VBComponents(x).CodeModule.CountOfLines
    End If
    Next x
End With
On Error GoTo 0
End Sub