Dear experts,
I have a VBA code in workbook "A" that writes VBA code in workbook "B".
The code looks like:

With ActiveWorkbook.VBProject.VBComponents("Sheet4").Co deModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
Etc.

It works on putting the code in Sheet4, but as Sheet4 is a created worksheet
by another macro, it is not always the sheet where I want the code!
Is there a way that I can tell VBA to put the code in the sheet called
"Data" instead than on Sheet4?

Thank you very much for your help.
Best regards
Valeria

It's easy to get the sheet's code module from its worksheet name:

Worksheets("Data").CodeName

--
Jim
"Valeria" wrote in message
...
| Dear experts,
| I have a VBA code in workbook "A" that writes VBA code in workbook "B".
| The code looks like:
|
| With ActiveWorkbook.VBProject.VBComponents("Sheet4").Co deModule
| StartLine = .CreateEventProc("Change", "Worksheet") + 1
| .InsertLines StartLine, _
| Etc.
|
| It works on putting the code in Sheet4, but as Sheet4 is a created
worksheet
| by another macro, it is not always the sheet where I want the code!
| Is there a way that I can tell VBA to put the code in the sheet called
| "Data" instead than on Sheet4?
|
| Thank you very much for your help.
| Best regards
| Valeria
|

Hi,
I have tried this:

With ActiveWorkbook.Worksheets("Data").CodeName
StartLine = .CreateEventProc("Change", "Worksheet") + 1
etc.

But I get the error "object required"...
Thanks for helping me!
Kind regards
--
Valeria


"Jim Rech" wrote:

It's easy to get the sheet's code module from its worksheet name:

Worksheets("Data").CodeName

--
Jim
"Valeria" wrote in message
...
| Dear experts,
| I have a VBA code in workbook "A" that writes VBA code in workbook "B".
| The code looks like:
|
| With ActiveWorkbook.VBProject.VBComponents("Sheet4").Co deModule
| StartLine = .CreateEventProc("Change", "Worksheet") + 1
| .InsertLines StartLine, _
| Etc.
|
| It works on putting the code in Sheet4, but as Sheet4 is a created
worksheet
| by another macro, it is not always the sheet where I want the code!
| Is there a way that I can tell VBA to put the code in the sheet called
| "Data" instead than on Sheet4?
|
| Thank you very much for your help.
| Best regards
| Valeria
|

You need something like

Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents( _
ActiveWorkbook.Worksheets("Sheet3").CodeName).Code Module
StartLine = .CreateEventProc("Change", "Worksheet")
End With



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Valeria" wrote in message
...
Hi,
I have tried this:

With ActiveWorkbook.Worksheets("Data").CodeName
StartLine = .CreateEventProc("Change", "Worksheet") + 1
etc.

But I get the error "object required"...
Thanks for helping me!
Kind regards
--
Valeria


"Jim Rech" wrote:

It's easy to get the sheet's code module from its worksheet
name:

Worksheets("Data").CodeName

--
Jim
"Valeria" wrote in message
...
| Dear experts,
| I have a VBA code in workbook "A" that writes VBA code in
workbook "B".
| The code looks like:
|
| With
ActiveWorkbook.VBProject.VBComponents("Sheet4").Co deModule
| StartLine = .CreateEventProc("Change", "Worksheet") + 1
| .InsertLines StartLine, _
| Etc.
|
| It works on putting the code in Sheet4, but as Sheet4 is a
created
worksheet
| by another macro, it is not always the sheet where I want
the code!
| Is there a way that I can tell VBA to put the code in the
sheet called
| "Data" instead than on Sheet4?
|
| Thank you very much for your help.
| Best regards
| Valeria
|

sName = ActiveWorkbook.Worksheets("Data").CodeName
With ActiveWorkbook.VBProject.VBComponents(sName).CodeM odule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _


--
Regards,
Tom Ogilvy

"Valeria" wrote in message
...
Hi,
I have tried this:

With ActiveWorkbook.Worksheets("Data").CodeName
StartLine = .CreateEventProc("Change", "Worksheet") + 1
etc.

But I get the error "object required"...
Thanks for helping me!
Kind regards
--
Valeria


"Jim Rech" wrote:

It's easy to get the sheet's code module from its worksheet name:

Worksheets("Data").CodeName

--
Jim
"Valeria" wrote in message
...
| Dear experts,
| I have a VBA code in workbook "A" that writes VBA code in workbook
"B".
| The code looks like:
|
| With ActiveWorkbook.VBProject.VBComponents("Sheet4").Co deModule
| StartLine = .CreateEventProc("Change", "Worksheet") + 1
| .InsertLines StartLine, _
| Etc.
|
| It works on putting the code in Sheet4, but as Sheet4 is a created
worksheet
| by another macro, it is not always the sheet where I want the code!
| Is there a way that I can tell VBA to put the code in the sheet called
| "Data" instead than on Sheet4?
|
| Thank you very much for your help.
| Best regards
| Valeria
|

This works wonderfully, thank you very much!
Best regards
Valeria
--
Valeria


"Tom Ogilvy" wrote:

sName = ActiveWorkbook.Worksheets("Data").CodeName
With ActiveWorkbook.VBProject.VBComponents(sName).CodeM odule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _


--
Regards,
Tom Ogilvy

"Valeria" wrote in message
...
Hi,
I have tried this:

With ActiveWorkbook.Worksheets("Data").CodeName
StartLine = .CreateEventProc("Change", "Worksheet") + 1
etc.

But I get the error "object required"...
Thanks for helping me!
Kind regards
--
Valeria


"Jim Rech" wrote:

It's easy to get the sheet's code module from its worksheet name:

Worksheets("Data").CodeName

--
Jim
"Valeria" wrote in message
...
| Dear experts,
| I have a VBA code in workbook "A" that writes VBA code in workbook
"B".
| The code looks like:
|
| With ActiveWorkbook.VBProject.VBComponents("Sheet4").Co deModule
| StartLine = .CreateEventProc("Change", "Worksheet") + 1
| .InsertLines StartLine, _
| Etc.
|
| It works on putting the code in Sheet4, but as Sheet4 is a created
worksheet
| by another macro, it is not always the sheet where I want the code!
| Is there a way that I can tell VBA to put the code in the sheet called
| "Data" instead than on Sheet4?
|
| Thank you very much for your help.
| Best regards
| Valeria
|