copy macros using VBA

hulei

New Member
Joined
Jun 23, 2010
Messages
11
hey !

I try to copy some macro codes into a new worksheet. This new worksheet is added and names after NewSheetName. I found a code, but this one was used to enter code in "tabelle1", which seems not to be a worksheet.

HTML:
http://vba1.de/vba/020makrorein.php

I tried to change "tabelle1" and insert the NewSheetName, but got the error "Subscript out of range"

Code:
Sub AddNewSheet

Dim NewSheetName as string

NewSheetName = Worksheets("blank").Range("B1").Value

´( .... )

Dim x As Integer

With ThisWorkbook.VBProject.VBComponents(NewSheetName).CodeModule
     x = .CreateEventProc("SelectionChange", "Worksheet") 
    .InsertLines x + 1, "'Test"
 
End With
End Sub

Do u have an idea what I have to change the code?

Thanks!
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

hulei

New Member
Joined
Jun 23, 2010
Messages
11
thanks for the quick reply!

I ve been on that site before, but I couldnt figure out how to copy the code to a specific worksheet ..

Am i overseeing something ?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
This worked for me (just as an example)

Code:
Sub CreateEventProcedure()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Const DQUOTE = """" ' one " character

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Sheet1")
Set CodeMod = VBComp.CodeModule

With CodeMod
    LineNum = .CreateEventProc("Activate", "Worksheet")
    LineNum = LineNum + 1
    .InsertLines LineNum, "    MsgBox " & DQUOTE & "Hello World" & DQUOTE
End With
End Sub
 

hulei

New Member
Joined
Jun 23, 2010
Messages
11

ADVERTISEMENT

looks great, thanks peter !
 

hulei

New Member
Joined
Jun 23, 2010
Messages
11
Sorry, still facing a hopefully minor problem

I tried to call the sheets I want to add the code then by a variable NewsheetName

I get the value for "NewSheetName" from a range on an other sheet.

If i check it using msgbox, it appears without " ", so I add parantheses and create a new name.

VBA somehow still cant address the sheet, and i get a "Subscript out of range" error. This doesnt happen when I use eg "Sheet31" as a reference.

The error occurs in the third line


Code:
NewSheetNameVBA = Quotes & Worksheets("help").Range("A31").Value & Quotes
 
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(newsheetname)
Set CodeMod = VBComp.CodeModule
With CodeMod
 
 
x = .CreateEventProc("change", "Worksheet")
    
    .insertLines x + 1, "ok le"
 
 End With

thanks
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
I think that you need the codename of the sheet. If you have just added a sheet, it should be the active sheet so maybe try

Rich (BB code):
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(ActiveSheet.CodeName)
Set CodeMod = VBComp.CodeModule
With CodeMod
 
 
x = .CreateEventProc("change", "Worksheet")
    
    .insertLines x + 1, "ok le"
 
 End With
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top