Woody19722
07-06-2012, 12:47 PM
I am currently using a workbook that has a hot button to save the current workbook into a new one. Listed be is the code:
Sub CopyMod()
Dim wkbSrc As Workbook, wkbDest As Workbook
Dim Btn As Button
Dim s As String
' set the source and destination workbooks
Set wkbSrc = Workbooks("MPO TOOL V7.1.0.xlsm")
Set wkbDest = Workbooks("Book1")
' get the code from the source module
With wkbSrc.VBProject.VBComponents("Module3").CodeModule
s = .Lines(1, .CountOfLines)
End With
' Create the destination module and copy the code
With wkbDest.VBProject.VBComponents.Add(1)
.Name = "SomeName"
With .CodeModule
If .CountOfLines > 0 Then .DeleteLines 1, .CountOfLines
.AddFromString s
End With
End With
'Set Btn = wkbDest.ActiveSheet.Buttons.Add(54.75, 32.25, 128.25, 61.5)
'With Btn
'.Caption = "Button Name" 'change the name of the button accordingly
'.OnAction = "msg"
'End With
End Sub
It crashes when you try to save it again, run-time error'9'. Subscript out of range. From my understanding it looks like it is trying to call it book2. This is creating a broken link because the code is trying to read book1. :dunno
Sub CopyMod()
Dim wkbSrc As Workbook, wkbDest As Workbook
Dim Btn As Button
Dim s As String
' set the source and destination workbooks
Set wkbSrc = Workbooks("MPO TOOL V7.1.0.xlsm")
Set wkbDest = Workbooks("Book1")
' get the code from the source module
With wkbSrc.VBProject.VBComponents("Module3").CodeModule
s = .Lines(1, .CountOfLines)
End With
' Create the destination module and copy the code
With wkbDest.VBProject.VBComponents.Add(1)
.Name = "SomeName"
With .CodeModule
If .CountOfLines > 0 Then .DeleteLines 1, .CountOfLines
.AddFromString s
End With
End With
'Set Btn = wkbDest.ActiveSheet.Buttons.Add(54.75, 32.25, 128.25, 61.5)
'With Btn
'.Caption = "Button Name" 'change the name of the button accordingly
'.OnAction = "msg"
'End With
End Sub
It crashes when you try to save it again, run-time error'9'. Subscript out of range. From my understanding it looks like it is trying to call it book2. This is creating a broken link because the code is trying to read book1. :dunno