| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I print a worksheet excluding blank rows? | Excel Worksheet Functions | |||
| Copying A Worksheet Only, Excluding the Code | Excel Programming | |||
| Copy worksheet excluding defined name ranges | Excel Programming | |||
| Printing worksheet excluding rows with formulas | Excel Programming | |||
| Print current worksheet excluding a few cells | Excel Programming | |||
Linear Mode
