Select Sheets Starting with "XXXX" and Remove Macro from Sheet

[JILBO]

Hi,

Utilising the code below to clear code from sheets named "Step1", Step2 etc

As the sheet named "Step" is dynamic I would like modify to capture all "Step" Shts



Sub export()

'Tools>References Microsoft Visual Basic For Applications Extensibility Library".

Dim VBProj As VBIDE.VBProject
Dim VBComp, sVBComp As VBIDE.VBComponent
Dim sSheet As Object, strName As String
'Delete code on sheets
For Each sSheet In Sheets
Select Case sSheet.Name

'I want an if(len statement? to basically apply to every sheet name begining with "Step"

Case "Step2", "Step3"

strName = sSheet.CodeName
With ThisWorkbook.VBProject.VBComponents(strName).CodeModule
.DeleteLines 1, .CountOfLines
End With
Case Else
'Nothing else
End Select
Next sSheet
End Sub

[Paul_Hossler]

Not tested, but similar solution to your other post



Option Explicit


Sub export()


'Tools>References Microsoft Visual Basic For Applications Extensibility Library".


Dim VBProj As VBIDE.VBProject
Dim VBComp, sVBComp As VBIDE.VBComponent
Dim ws As Worksheet

'Delete code on sheets
For Each ws In Worksheets
If UCase(Left(ws.Name, 4)) = "STEP" Then
With ThisWorkbook.VBProject.VBComponents(ws.Name).CodeModule
.DeleteLines 1, .CountOfLines
End With
End If
Next


End Sub

[JILBO]

Thanks Once again Paul

Gives an Supcript9 error on line ""With ThisWorkbook.VBProject.VBComponents(ws.Name).CodeModule" Any ideas???

[Paul_Hossler]

not without seeing an example workbook

[JILBO]

See attached

[Paul_Hossler]

Option Explicit


Sub export1()
Dim ws As Worksheet
Dim VBProj As VBProject


Set VBProj = ThisWorkbook.VBProject


For Each ws In ThisWorkbook.Worksheets
If UCase(Left(ws.Name, 4)) = "STEP" Then
With VBProj.VBComponents(ws.CodeName).CodeModule
.DeleteLines 1, .CountOfLines
End With
End If
Next
End Sub

[SamT]

Copy sheet without CodePage

With NewWorkbook
For each Sht in ThisWorkbook.Sheets
If Sht.Name like "Step*" 'Edit to suit
.Sheets.Add
.ActiveSheet.Name = Sht.Name
Sht.Cells.Copy 'Don't copy CodePage
.ActiveSheet.Cells(1).PasteSpecial
End If
Next Sht
End With

[JILBO]

That’s great thank you gents !! Works perfectly