Copy Worksheet without its code

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Jul 2, 2003

• #2

You could insert a new workbook that has only one sheet, and then paste as values the "original" sheet....

 

Upvote 0

Smitty

Legend

Joined

May 15, 2003

Messages

29,536

• Jul 2, 2003

• #3

I believe this will do what you want:

ActiveSheet.Copy
With ActiveWorkbook
.SaveAs "NewFileName.xls", FileFormat:=xlNormal
End With

Hope that helps,

Smitty

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Jul 2, 2003

• #4

No, I thought that too, but it you try it, it will copy the worksheet module with the code in it too... which, well, I don't know. I guess it depends on how the OP has it set up !

 

Upvote 0

Smitty

Legend

Joined

May 15, 2003

Messages

29,536

• Jul 2, 2003

• #5

Weird,

I have a file with a bunch of code in it and none of the individual sheet or ThisWorkbook code copy with that method.

I don't think I've done anything specific to prevent copying.

Smitty

 

Upvote 0

Tom Urtis

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,300

• Jul 2, 2003

• #6

Perhaps this might work. If not please post back.

Sub Test2()
Dim wkb As Workbook
Set wkb = Workbooks.Add
ThisWorkbook.Worksheets("Sheet1").Copy after:=wkb.Worksheets(wkb.Worksheets.count)
With ActiveSheet
With .Parent.VBProject.VBComponents(.CodeName)
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End With
End With
End Sub

 

Upvote 0

J

Juan Pablo González

MrExcel MVP

Joined

Feb 8, 2002

Messages

11,959

• Jul 2, 2003

• #7

Tom, as the OP said, that is very likely to fail in XP because of the new security setting, that is checked by default, that disables access to the VB Project.

 

Upvote 0

S

stemar

Board Regular

Joined

Mar 16, 2002

Messages

248

• Jul 2, 2003

• #8

Thanks to all.

I've gone with the workbooks.add and copy method.

It needed the following steps:

Turn calculation off (I have custom functions in there, just to complicate matters!)

Copy the entire original worksheet

Paste into new wkbook (Copies formatting and formulas)

Copy the new worksheet

PasteSpecial values (replaces formulas that refer to other worksheets with values)

Turn calculation back on

 

Upvote 0

Tom Urtis

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,300

• Jul 2, 2003

• #9

Hey JPG, yep I skipped over that part of the OP's request.

I generally like XP but the logic of that new feature escapes me in cases like this - - where you need to ask the user to give you access to the project to prevent them from accessing the code in the project. Go figure.

 

Upvote 0