Create a newworksheet with VBA code and put VBA code in the new worksheet module

[ceshelman]

I need to use VBA to create a new sheet (got that part taken care of
but then I need to add a Private Sub Worksheet_Change(ByVal Target A
Range) subroutine to the page that I have just created. How do I g
about accomplishing that

 

[Norie]

Check this link 'Programming To The Visual Basic Editor
(http://www.cpearson.com/excel/vbe.htm

 

[Guest]

This is easy if you use the Move or Copy routine when creating the new sheet.
Create a "template" sheet with your desired layout and worksheet change code.
You can hide this sheet if you desire.

Now have VBA copy this sheet for your new sheet and the code will already be
there.

Something similar to:

Sheets("YourTemplateSheet").Copy Before:=Sheets(1)

 

[mp112849]

Damon, will this work if you want to copy code into the "ThisWorkbook"
module of a second workbook? In other words, here's a scenario...

I have a workbook open, call it WorkBookA. I open another existing
workbook programatically or with file/open, call it WorkBookB. Can I
copy code from the ThisWorkbook module of WorkBookA to the ThisWorkBook
module of WorkBookB? Can this code be an overwrite of an existing
method in WorkBookB, say a pre-existing ThisWorkbook sub like:

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal
Target As Range, Cancel As Boolean)
.... code goes here
End Sub

 

[Guest]

Yes, it is possible. Follow the link provided by Norie for instructions. Read
the whole page so you have a good understanding of the pros and cons of
writing code with code.

I personally, will create a template with the code already present and then
add data to this template with code. I prefer the K.I.S.S. principle.