PDA

View Full Version : Solved: How do I delete code from a sheet module under program control?



xltrader100
07-29-2008, 02:54 PM
How can I delete the code in a sheet module before copying the sheet to a new workbook?

Mavyak
07-29-2008, 03:01 PM
It's usually a real pain in the keister. Here's a pretty good reference, though:

http://www.cpearson.com/excel/vbe.aspx

Bob Phillips
07-29-2008, 03:10 PM
With ActiveWorkbook.VBProject.VBComponents("Sheet2").CodeModule
.DeleteLines 1, .CountOfLines
End With

Mavyak
07-29-2008, 04:35 PM
That's why xld gets the big bucks. :beerchug:

xltrader100
07-29-2008, 05:58 PM
I'll second Mavyak's keister comments, after spending another hour on it. It turns out that snippet only works with the sheet's code name. Doesn't work with the Tab name or the Index number. So, what developed was:


With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
.DeleteLines 1, .CountOfLines
End With

I realize it's dangerous to leave it like that, but just for illustration.

Other than that, it works great. Many thanks.

However, it uncovered another problem that probably still belongs in this thread. When I copy the sheet, and before I get a chance to strip the code, the VBA compiler starts checking the copy. And if the original had been saved with VBA errors then you know what happens.

So, how do I prevent the compiler from checking anything until I've had a chance to run that neat snippet above?

Bob Phillips
07-30-2008, 01:39 AM
There was a thread recently with Hoopsah where he wanted to do asimilar thing, copy a sheet to a new workbook, strip the code, and save it, and didn't have a problem.

Is that what you are doing? If so, can you post the book?

xltrader100
07-30-2008, 05:23 AM
I found the solution to my code copying problem in the Hoopsah thread you mentioned. It was the suggestion by RichardSchollar that to avoid copying the code over, just copy the sheet contents instead of the whole sheet. Well, Of Course!! If it hurts to do it, then don't do it.:clap:





Dim newBookName As String
newBookName = Workbooks.Add.Name 'default is BookX
Workbooks(ActiveWorkbook.Name).Sheets(ActiveSheet.Name).UsedRange.Copy
Workbooks(newBookName).Sheets("Sheet1").Paste
Workbooks(newBookName).Activate


Works a treat except without that final Activate, Excel locks up solid.

Thanks again, guys. Definitely solved.