Changing sheet content using codename

[Guest]

Hi,
I have a problem using sheetscodename. I have changed some of the
sheetcodenames from "Sheetxxx" to totally different. Now I need to clear
contents of the codenames that begins with Sheet and includes number in the
end. How could I do that. I have now code that clears the content of ALL
sheets and I do not want that to happen.... (also if there is a better way
to program code below all comments welcome)

Thanks for helping me ;o)
Makelei

the code:

Sub CleanReports()


Dim ws As Worksheet
Dim Pic As Shape

Application.ScreenUpdating = False

'************************* Goes trough reports and cleans the content

Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
If ws.CodeName > "Sheet" Then ws.Activate

Columns("AA").Select
Selection.ClearContents
Range("A1").Select

For Each Pic In ActiveSheet.Shapes
If Pic.Type = msoPicture Then
Pic.Delete
End If
Next Pic

Next ws
Application.DisplayAlerts = True

Sheets("ProgIDs").Select
Range("D27").Select
End Sub

 

[Nigel]

For the sheets where you have not changed a codename these will also have a
codename of sheetxxx, the default. I would use a different value for the
sheet names where you wish this to apply (I choose Rep in the following
code), also include the require sheet codename more explicitly (I check for
sheets beginning with Rep). I have also tidied the code as requested.

Sub CleanReports()


Dim ws As Worksheet
Dim pic As Shape

Application.ScreenUpdating = False

'************************* Goes trough reports and cleans the content

Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
If Left(ws.CodeName, 3) = "Rep" Then
With ws
.Columns("AA").ClearContents
For Each pic In .Shapes
If pic.Type = msoPicture Then pic.Delete
Next pic
End With
End If
Next ws
Application.DisplayAlerts = True

Sheets("ProgIDs").Select
Range("D27").Select
End Sub

 

[Guest]

Hi Nigel,
Thanks - It works great!

Makelei

For the sheets where you have not changed a codename these will also have a
codename of sheetxxx, the default. I would use a different value for the
sheet names where you wish this to apply (I choose Rep in the following
code), also include the require sheet codename more explicitly (I check for
sheets beginning with Rep). I have also tidied the code as requested.

Sub CleanReports()


Dim ws As Worksheet
Dim pic As Shape

Application.ScreenUpdating = False

'************************* Goes trough reports and cleans the content

Application.DisplayAlerts = False
For Each ws In ActiveWorkbook.Worksheets
If Left(ws.CodeName, 3) = "Rep" Then
With ws
.Columns("AA").ClearContents
For Each pic In .Shapes
If pic.Type = msoPicture Then pic.Delete
Next pic
End With
End If
Next ws
Application.DisplayAlerts = True

Sheets("ProgIDs").Select
Range("D27").Select
End Sub