PDA

View Full Version : Solved: Copy sheets but without the code..



Thymen
02-27-2006, 02:57 PM
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?

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

Greetings,

Thymen

Jacob Hilderbrand
02-27-2006, 03:05 PM
Just do a copy/paste of the cells instead. Something like this.


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")

Thymen
02-27-2006, 04:02 PM
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
02-27-2006, 07:18 PM
Glad to help :beerchug:

Take Care

Zack Barresse
02-27-2006, 10:42 PM
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).