|
Excluding Worksheet from Procedure
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 |
Excluding Worksheet from Procedure
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 "Henry Stockbridge" wrote: 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 |
Excluding Worksheet from Procedure
Perfect, thanks!
JLGWhiz wrote: 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 "Henry Stockbridge" wrote: 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 |
| All times are GMT +1. The time now is 07:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com