Excluding Worksheet from Procedure

H

Henry Stockbridge

Hi,

I will be getting 30 - 40 Excel workbooks each with two sheets each. I
would like to run code on only the named sheets not ending with the
letter 'c.' Any help you can lend with this would be appreciated.

'--------------------------------------------------------------

Dim MyFile
Dim MyPath
Dim MyName

MyPath = "c:\ExcelFiles\"
MyName = Dir(MyPath, vbNormal)

Do While MyName <> ""
If (GetAttr(MyPath & MyName) And vbNormal) = vbNormal Then
Application.DisplayAlerts = False
Application.Workbooks.Open (MyPath & MyName)
For Each Worksheet In ActiveWorkbook.Worksheets
If Right(Worksheet.Name, 1) = "c" Then
GoTo ExitLoop
Else

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Replace What:=" ", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="#", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:=".", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("A:Ai").Select
Selection.NumberFormat = "@"
Columns("v:v").Select
Selection.NumberFormat = "#"

ActiveWorkbook.Save

ActiveWorkbook.SaveAs
Filename:="C:\ExcelFiles\Cleaned\" & Worksheet.Name & ".xls", _
FileFormat:=xlNormal, CreateBackup:=False
End If
ExitLoop:
Next Worksheet
ActiveWorkbook.Close False
End If
MyName = Dir
Loop

End Sub

'------------------------------------

Henry
 
J

JLGWhiz

Untested, but something like this. You can put it in a For each ws Next loop.

If LCase(Right(Worksheet.name,1)) <> "c" then
'Do something
End If
 
H

Henry Stockbridge

Perfect, thanks!
Untested, but something like this. You can put it in a For each ws Next loop.

If LCase(Right(Worksheet.name,1)) <> "c" then
'Do something
End If
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top