How tp copy a worksheet withoput copying maco

[smiler44]

Sorry if asked before.
I'm using Sheets("Dashboard").Copy Before:=Workbooks("Vice President.xls").Sheets(1) to copy a work sheet from one workbook to another. Both workbooks are open.
Unfortunately it seems that the VBA code from the Dashboard sheet also gets copied. Is there a way to stop the macros, sub routines etc being copied?

smiler44

 

[HaHoBe]

Hi, smiler44,

if you use OfficeXP/2002 or later you may have to check the access for the vba project under security:

Dim wks As Worksheet
Sheets("Dashboard").Copy Before:=Workbooks("Vice President.xls").Sheets(1)
Set wks = ActiveSheet
With ActiveWorkbook.VBProject _
   .VBComponents(wks.CodeName).CodeModule
   .DeleteLines 1, .CountOfLines
End With
Set wks = Nothing

Ciao,
Holger

 

[smiler44]

Ciao,
could you tell me what should codename and codemodule be?
I'm getting a runtime error 1004, Application Defined or object defined error. I'm using Windows XP and Excel 2003. I've check security and nothing seems to jump out at me that I need to change anything

smiler44

 

[HaHoBe]

Hi, smiler44,

Codename is the internal name of the object as you can see in the Project Explorer (should be something like Sheet1, not the name which can be altered in the sheet itself), and CodeModule just relies on the module for that particular object.

There are two checkboxes on the second side of the security box, and both are checked in my version - no run time error shows up (Excel2003 SP3 German, that being a part of Office2003 Professional/Windows2003 Server SP2 as well as Excel97 SR2/WindowsXP Sp3).

Ciao,
Holger

 

[smiler44]

Ciao,
The two checkboxes, if these are under the Trusted Publishers tab I have been told I can not use the Trust Access to Visual Basic Project option as the end user will need to make this alteration on their PC as well. My "boss" does not want to have the end user have to make any changes like this. If I could use it life would be simple and the problem solved.

smiler44

 

[HaHoBe]

Re: How to copy a worksheet withoput copying maco

Hi, smiler44,

the other way of not copying the sheet directly but looping through the UsedRange to copy values and formulas to a "master" being used instead without code won´t please me. Maybe anybody else has a workaround for this problem (except to alter the settings in the registry in the process of setting up the workbook).

Ciao,
Holger

 

[smiler44]

I think I have stumbled across the answer. Keep macros and routines in a module and not in the work sheet.

smiler44