Adding code to worksheet module

[CBrine]

This is the basic code for doing what you want. Make your new workbook the activeworkbook and run this code. The code that is added in in the form of one long string. That's why I have a line of code then & CHR(13) & Next line of code. Let me know if you have any problem implementing it.

NOTE: Make sure you save prior to running any of this code. If you get something that the vba editor would choke on, then Excel will crash(At least in 97). You will also need to set a reference to VBA Extensibility lib.(Or change the code to late binding)

HTH

Dim WB1 As Workbook
Dim WS1 As Worksheet
Dim MD As VBComponent
Dim PD As CodeModule
Dim LineNumber As Long
Dim CmdBut As msforms.CommandButton
Set WB1 = ActiveWorkbook
Set WS1 = ActiveSheet
'Sets up a new module based on MD
Set MD = WB1.VBProject.VBComponents.Add(vbext_ct_StdModule)
MD.Name = "Module1"
Application.Visible = True
'Add Code to Module
Set PD = WB1.VBProject.VBComponents(MD.Name).CodeModule
With PD
    LineNumber = .CountOfLines + 1
       .InsertLines LineNumber, _
    "Public BarNames(30) as String" & Chr(13) & "Public Counter As Integer" & Chr(13) & _
End With