View Full Version : Solved: Delete Code After Copy
jammer6_9
02-10-2008, 07:14 AM
How can I delete the codes on the copied sheets. Below code is what i use to copy selected sheets to a new workbook but it include the codes on the sheet wherein I dont want them to show on the new workbook.
Dim wb As New Workbook
Dim sh As Worksheet
Dim sFile As Variant
Sheets(Array("Audit Summary", "Allowables", "Star Hunt", "EDSSvsREST", "VOIDS")).Copy
Set wb = ActiveWorkbook
sFile = Application.GetSaveAsFilename(InitialFileName:="BK Audit Summary Report", _
fileFilter:="Microsoft Office Excel Workbook (*.xls), *.xls", Title:="Save workbook to..")
If sFile <> False Then
ActiveWorkbook.SaveAs Filename:=sFile
End If
Bob Phillips
02-10-2008, 07:32 AM
'----------------------------------------------------------------
Sub DeleteProcedure()
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim iStart As Long
Dim cLines As Long
Set oCodeModule = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
With oCodeModule
On Error GoTo dp_err:
iStart = .ProcStartLine("myProc", 0)
cLines = .ProcCountLines("myProc", 0)
.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
jammer6_9
02-10-2008, 11:39 PM
iStart = .ProcStartLine("myProc", 0) ' Im having error on this line thats why it goes directly to;
dp_err:
If Err.Number = 35 Then
MsgBox "Procedure does not exist"
End If
'----------------------------------------------------------------
Sub DeleteProcedure()
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim iStart As Long
Dim cLines As Long
Set oCodeModule = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
With oCodeModule
On Error GoTo dp_err:
iStart = .ProcStartLine("myProc", 0)
cLines = .ProcCountLines("myProc", 0)
.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
jammer6_9
02-10-2008, 11:47 PM
:bow: Sorry xld the code works... Thanks...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.