Delete statements in the Module

[Guest]

I have a program that save an excel file.
I want to delete from "start move the button" to "end move the button"
statement(see program below) after the macro was run. So I will not have this
statement in the module when I open the new file.
Could you please help with this matter

Thank in advance.
Maperalia

'**********************************************
Function SaveExcelTemplatelAsSaveAs() As String

Dim WO As String
Dim grdprp As String
Dim sFilename As String
Dim Progname As String
Dim Filename As String
Dim myDateTime As String

WO = Worksheets("summary BLR").Range("M10")
myDateTime = Format(Worksheets("summary BLR").Range("M9").Value, "yyyymmdd")
Filename = "" & WO & "_grdprp_" & myDateTime & ""
Progname = "C:\Mario\VB\Excel\Copy from Excel to Word\" & Filename & ".xls"
ActiveWorkbook.SaveCopyAs Progname

SaveExcelTemplatelAsSaveAs = Progname

'******** 'Start Move the Buttons****************
ActiveSheet.Shapes("Rectangle 4").Select
Selection.ShapeRange.IncrementLeft -3.75
Selection.ShapeRange.IncrementTop 889.5
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveSheet.Shapes("Rectangle 2").Select
Selection.ShapeRange.IncrementLeft 0.75
Selection.ShapeRange.IncrementTop -338.25
'******** 'Start Move the Buttons****************



'*********************************************
'OPEN THE FILENAME
Workbooks.Open Filename:=Progname
'**********************************************
End Function
'**********************************************

 

[Bob Phillips]

Put the variable stuff in a separate procedure, and delete the procedure

Const vbext_pk_Proc = 0

'----------------------------------------------------------------
Sub DeleteProcedure()
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim iStart As Long
Dim cLines As Long

Set oCodeModule =
ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
With oCodeModule
On Error GoTo dp_err:
iStart = .ProcStartLine("myProc", vbext_pk_Proc)
cLines = .ProcCountLines("myProc", vbext_pk_Proc)
.DeleteLines iStart, cLines
On Error GoTo 0
Exit Sub
End With

dp_err:
If Err.Number = 35 Then
MsgBox "Procedure does not exist"
End If
End Sub

then create it as a new empty version

'----------------------------------------------------------------
Sub AddModuleProc()
'----------------------------------------------------------------
Dim StartLine As Long
Dim cLines As Long

With ActiveWorkbook.VBProject.VBComponents("Module1").CodeModule
cLines = .CountOfLines + 1
.InsertLines cLines, _
"Sub myProc()" & Chr(13) & _
"End Sub"
End With
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob;
Thank for your quick respond.
It is working perfectly!!!!

Best regards.
Maperalia