Solved: Copy sheets but without the code..

[Thymen]

Hi folks,

I use the code below to copy sheets from the active workbook to another. However... that if that sheet contains VB code, that is also copied. Is there an option to copy WITHOUT code?

[VBA]Sub CopySheets()
Dim BkName As String
Dim NumSht As Integer
Dim BegSht As Integer
BegSht = 1
NumSht = 6
BkName = ActiveWorkbook.Name
For x = 1 To NumSht
Workbooks(BkName).Sheets(BegSht).Copy _
Before:=Workbooks("Test.xls").Sheets(1)
Next
End Sub[/vba]

Greetings,

Thymen

[Jacob Hilderbrand]

Just do a copy/paste of the cells instead. Something like this.

[vba]
Workbooks("Test.xls").Sheets.Add Before:=Workbooks("Test.xls").Sheets(1)
Workbooks(BkName).Sheets(BegSht).Cells.Copy _
Destination:=Workbooks("Test.xls").Sheets(1).Range("A1")

[/vba]

[Thymen]

Thanks!
I used yr sample to create some new code. I added an add-sheet and rename-sheet to it. That is why I tried with the copy sheet way first: it adds a sheet while retaining the original name.
Anyhow, it's solved. I'll put the new code in here tomorrow. Bedtime now....

Thymen

[Jacob Hilderbrand]

Glad to help

Take Care

[Zack Barresse]

I generally recommend copying the sheet and deleting any code. Copy/paste has some deficiencies, plus I think it's just clunky. There is some good code in the KB and around the forum on deleting code from worksheets and such. Also, Chip Pearson's site has a good section on deleting code from a file/module(s).