| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel macro missing worksheets
I have this excel file with multiple worksheets, I created a macro
that goes into each worksheet and formats a whole lot of stuff. The problem is that some times the excel file does not have all the worksheets included, when this happens and I try to run the macro, I get errors because of the missing worksheets and the macro does not finish is there a way to get the macro to skip the set of commands if the worksheet is missing, so it would go to the next worksheet and continue the macro? listed below is a piece of the macro, ending with the selection of the next worksheet. Sheets("550").Select Columns("A:R").Select Range("A13").Activate Selection.EntireColumn.Hidden = False Columns("A:A").Select Range("A13").Activate Selection.Delete Shift:=xlToLeft Columns("B:B").Select Range("B13").Activate Selection.Delete Shift:=xlToLeft Columns("E:E").ColumnWidth = 4.56 Columns("E:E").ColumnWidth = 5.67 Columns("E:E").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("V:V").Select Selection.Cut Columns("E:E").Select ActiveSheet.Paste Columns("W:W").Select Selection.Cut Columns("F:F").Select ActiveSheet.Paste Columns("S:S").Select Selection.Cut Columns("G:G").Select ActiveSheet.Paste Columns("T:T").Select Selection.Cut Columns("I:I").Select ActiveSheet.Paste Columns("AB:AB").Select Selection.Cut Columns("L:L").Select ActiveSheet.Paste Range("M6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]*1.5)" Range("M6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 12).Select Range("M7", ActiveCell).Select ActiveSheet.Paste Range("N6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-2]*2)" Range("N6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 13).Select Range("N7", ActiveCell).Select ActiveSheet.Paste Columns("AD:AD").Select Application.CutCopyMode = False Selection.Cut Columns("Q:Q").Select ActiveSheet.Paste Columns("S:AD").Select Selection.Delete Shift:=xlToLeft Columns("Q:Q").Select Selection.Replace What:="CA", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Select Range("E1").Activate Selection.NumberFormat = "@" Sheets("725").Select |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
excel macro missing worksheets
Apart from the fact that this macro is terribly inefficient, just put:
On Error Resume Next before the block of code and On Error Goto 0 after the block of code. If you describe exactly what you are trying to do, I bet this code could be reduced to half-a-dozen lines. __________________________________________________ _____________________ wrote in message oups.com... I have this excel file with multiple worksheets, I created a macro that goes into each worksheet and formats a whole lot of stuff. The problem is that some times the excel file does not have all the worksheets included, when this happens and I try to run the macro, I get errors because of the missing worksheets and the macro does not finish is there a way to get the macro to skip the set of commands if the worksheet is missing, so it would go to the next worksheet and continue the macro? listed below is a piece of the macro, ending with the selection of the next worksheet. Sheets("550").Select Columns("A:R").Select Range("A13").Activate Selection.EntireColumn.Hidden = False Columns("A:A").Select Range("A13").Activate Selection.Delete Shift:=xlToLeft Columns("B:B").Select Range("B13").Activate Selection.Delete Shift:=xlToLeft Columns("E:E").ColumnWidth = 4.56 Columns("E:E").ColumnWidth = 5.67 Columns("E:E").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("V:V").Select Selection.Cut Columns("E:E").Select ActiveSheet.Paste Columns("W:W").Select Selection.Cut Columns("F:F").Select ActiveSheet.Paste Columns("S:S").Select Selection.Cut Columns("G:G").Select ActiveSheet.Paste Columns("T:T").Select Selection.Cut Columns("I:I").Select ActiveSheet.Paste Columns("AB:AB").Select Selection.Cut Columns("L:L").Select ActiveSheet.Paste Range("M6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]*1.5)" Range("M6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 12).Select Range("M7", ActiveCell).Select ActiveSheet.Paste Range("N6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-2]*2)" Range("N6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 13).Select Range("N7", ActiveCell).Select ActiveSheet.Paste Columns("AD:AD").Select Application.CutCopyMode = False Selection.Cut Columns("Q:Q").Select ActiveSheet.Paste Columns("S:AD").Select Selection.Delete Shift:=xlToLeft Columns("Q:Q").Select Selection.Replace What:="CA", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Select Range("E1").Activate Selection.NumberFormat = "@" Sheets("725").Select |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
excel macro missing worksheets
Put this User-Defined Function in the Module...
'/================================/ Public Function WorkSheetExists(strWkshtName As String) As Boolean 'returns true if worksheet exists in the active workbook Dim objWorksheet As Object On Error Resume Next WorkSheetExists = False Set objWorksheet = ActiveWorkbook.Sheets(strWkshtName) If Err = 0 Then WorkSheetExists = True End If Set objWorksheet = Nothing End Function '/================================/ Put your formatting inside an if statement such as... If WorkSheetExists("550") = True then 'all your formatting End If If WorkSheetExists("725") = True then 'all your formatting End If etc... -- HTH, Gary Brown If this post was helpful to you, please select ''YES'' at the bottom of the post. " wrote: I have this excel file with multiple worksheets, I created a macro that goes into each worksheet and formats a whole lot of stuff. The problem is that some times the excel file does not have all the worksheets included, when this happens and I try to run the macro, I get errors because of the missing worksheets and the macro does not finish is there a way to get the macro to skip the set of commands if the worksheet is missing, so it would go to the next worksheet and continue the macro? listed below is a piece of the macro, ending with the selection of the next worksheet. Sheets("550").Select Columns("A:R").Select Range("A13").Activate Selection.EntireColumn.Hidden = False Columns("A:A").Select Range("A13").Activate Selection.Delete Shift:=xlToLeft Columns("B:B").Select Range("B13").Activate Selection.Delete Shift:=xlToLeft Columns("E:E").ColumnWidth = 4.56 Columns("E:E").ColumnWidth = 5.67 Columns("E:E").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("V:V").Select Selection.Cut Columns("E:E").Select ActiveSheet.Paste Columns("W:W").Select Selection.Cut Columns("F:F").Select ActiveSheet.Paste Columns("S:S").Select Selection.Cut Columns("G:G").Select ActiveSheet.Paste Columns("T:T").Select Selection.Cut Columns("I:I").Select ActiveSheet.Paste Columns("AB:AB").Select Selection.Cut Columns("L:L").Select ActiveSheet.Paste Range("M6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]*1.5)" Range("M6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 12).Select Range("M7", ActiveCell).Select ActiveSheet.Paste Range("N6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-2]*2)" Range("N6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 13).Select Range("N7", ActiveCell).Select ActiveSheet.Paste Columns("AD:AD").Select Application.CutCopyMode = False Selection.Cut Columns("Q:Q").Select ActiveSheet.Paste Columns("S:AD").Select Selection.Delete Shift:=xlToLeft Columns("Q:Q").Select Selection.Replace What:="CA", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Select Range("E1").Activate Selection.NumberFormat = "@" Sheets("725").Select |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
excel macro missing worksheets
This is the second time I'm responding. Not sure why my previous posting is
listed For Each mysheet In Worksheets Select Case mysheet.Name Case "550" Sheets("550").Select Columns("A:R").Select Range("A13").Activate Selection.EntireColumn.Hidden = False Columns("A:A").Select Range("A13").Activate Selection.Delete Shift:=xlToLeft Columns("B:B").Select Range("B13").Activate Selection.Delete Shift:=xlToLeft Columns("E:E").ColumnWidth = 4.56 Columns("E:E").ColumnWidth = 5.67 Columns("E:E").Select Selection.Insert Shift:=xlToRight, _ CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, _ CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, _ CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, _ CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, _ CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, _ CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, _ CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, _ CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, _ CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, _ CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, _ CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, _ CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, _ CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, _ CopyOrigin:=xlFormatFromLeftOrAbove Columns("V:V").Select Selection.Cut Columns("E:E").Select ActiveSheet.Paste Columns("W:W").Select Selection.Cut Columns("F:F").Select ActiveSheet.Paste Columns("S:S").Select Selection.Cut Columns("G:G").Select ActiveSheet.Paste Columns("T:T").Select Selection.Cut Columns("I:I").Select ActiveSheet.Paste Columns("AB:AB").Select Selection.Cut Columns("L:L").Select ActiveSheet.Paste Range("M6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]*1.5)" Range("M6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 12).Select Range("M7", ActiveCell).Select ActiveSheet.Paste Range("N6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-2]*2)" Range("N6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 13).Select Range("N7", ActiveCell).Select ActiveSheet.Paste Columns("AD:AD").Select Application.CutCopyMode = False Selection.Cut Columns("Q:Q").Select ActiveSheet.Paste Columns("S:AD").Select Selection.Delete Shift:=xlToLeft Columns("Q:Q").Select Selection.Replace What:="CA", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Select Range("E1").Activate Selection.NumberFormat = "@" Case "550" Sheets("725").Select End Select Next mysheet " wrote: I have this excel file with multiple worksheets, I created a macro that goes into each worksheet and formats a whole lot of stuff. The problem is that some times the excel file does not have all the worksheets included, when this happens and I try to run the macro, I get errors because of the missing worksheets and the macro does not finish is there a way to get the macro to skip the set of commands if the worksheet is missing, so it would go to the next worksheet and continue the macro? listed below is a piece of the macro, ending with the selection of the next worksheet. Sheets("550").Select Columns("A:R").Select Range("A13").Activate Selection.EntireColumn.Hidden = False Columns("A:A").Select Range("A13").Activate Selection.Delete Shift:=xlToLeft Columns("B:B").Select Range("B13").Activate Selection.Delete Shift:=xlToLeft Columns("E:E").ColumnWidth = 4.56 Columns("E:E").ColumnWidth = 5.67 Columns("E:E").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("V:V").Select Selection.Cut Columns("E:E").Select ActiveSheet.Paste Columns("W:W").Select Selection.Cut Columns("F:F").Select ActiveSheet.Paste Columns("S:S").Select Selection.Cut Columns("G:G").Select ActiveSheet.Paste Columns("T:T").Select Selection.Cut Columns("I:I").Select ActiveSheet.Paste Columns("AB:AB").Select Selection.Cut Columns("L:L").Select ActiveSheet.Paste Range("M6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]*1.5)" Range("M6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 12).Select Range("M7", ActiveCell).Select ActiveSheet.Paste Range("N6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-2]*2)" Range("N6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 13).Select Range("N7", ActiveCell).Select ActiveSheet.Paste Columns("AD:AD").Select Application.CutCopyMode = False Selection.Cut Columns("Q:Q").Select ActiveSheet.Paste Columns("S:AD").Select Selection.Delete Shift:=xlToLeft Columns("Q:Q").Select Selection.Replace What:="CA", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Select Range("E1").Activate Selection.NumberFormat = "@" Sheets("725").Select |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
excel macro missing worksheets
Several references to help you along:
http://www.dailydoseofexcel.com/arch...8/sheetexists/ http://j-walk.com/ss//excel/tips/tip54.htm#func5 http://saltwetfish.wordpress.com/200...l-sheetexists/ http://xlvba.3.forumer.com/index.php...howt opic=235 -- p45cal " wrote: I have this excel file with multiple worksheets, I created a macro that goes into each worksheet and formats a whole lot of stuff. The problem is that some times the excel file does not have all the worksheets included, when this happens and I try to run the macro, I get errors because of the missing worksheets and the macro does not finish is there a way to get the macro to skip the set of commands if the worksheet is missing, so it would go to the next worksheet and continue the macro? listed below is a piece of the macro, ending with the selection of the next worksheet. Sheets("550").Select Columns("A:R").Select Range("A13").Activate Selection.EntireColumn.Hidden = False Columns("A:A").Select Range("A13").Activate Selection.Delete Shift:=xlToLeft Columns("B:B").Select Range("B13").Activate Selection.Delete Shift:=xlToLeft Columns("E:E").ColumnWidth = 4.56 Columns("E:E").ColumnWidth = 5.67 Columns("E:E").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("V:V").Select Selection.Cut Columns("E:E").Select ActiveSheet.Paste Columns("W:W").Select Selection.Cut Columns("F:F").Select ActiveSheet.Paste Columns("S:S").Select Selection.Cut Columns("G:G").Select ActiveSheet.Paste Columns("T:T").Select Selection.Cut Columns("I:I").Select ActiveSheet.Paste Columns("AB:AB").Select Selection.Cut Columns("L:L").Select ActiveSheet.Paste Range("M6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]*1.5)" Range("M6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 12).Select Range("M7", ActiveCell).Select ActiveSheet.Paste Range("N6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-2]*2)" Range("N6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 13).Select Range("N7", ActiveCell).Select ActiveSheet.Paste Columns("AD:AD").Select Application.CutCopyMode = False Selection.Cut Columns("Q:Q").Select ActiveSheet.Paste Columns("S:AD").Select Selection.Delete Shift:=xlToLeft Columns("Q:Q").Select Selection.Replace What:="CA", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Select Range("E1").Activate Selection.NumberFormat = "@" Sheets("725").Select |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
excel macro missing worksheets
Something like this might work:
For Each sh In ActiveWorkbook If sh = Sheets("550") Then 'Insert your code for 550 End If If sh = Sheets("725") Then 'Insert your code for 725 End if Next You should try to eliminate some of the selects and activate like the sample below: Sheets("550").Columns("A:R").Select Range("A13").EntireColumn.Hidden = False Range("A13").Delete Shift:=xlToLeft Range("B13").Delete Shift:=xlToLeft Columns("E:E").ColumnWidth = 5.67 " wrote: I have this excel file with multiple worksheets, I created a macro that goes into each worksheet and formats a whole lot of stuff. The problem is that some times the excel file does not have all the worksheets included, when this happens and I try to run the macro, I get errors because of the missing worksheets and the macro does not finish is there a way to get the macro to skip the set of commands if the worksheet is missing, so it would go to the next worksheet and continue the macro? listed below is a piece of the macro, ending with the selection of the next worksheet. Sheets("550").Select Columns("A:R").Select Range("A13").Activate Selection.EntireColumn.Hidden = False Columns("A:A").Select Range("A13").Activate Selection.Delete Shift:=xlToLeft Columns("B:B").Select Range("B13").Activate Selection.Delete Shift:=xlToLeft Columns("E:E").ColumnWidth = 4.56 Columns("E:E").ColumnWidth = 5.67 Columns("E:E").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("V:V").Select Selection.Cut Columns("E:E").Select ActiveSheet.Paste Columns("W:W").Select Selection.Cut Columns("F:F").Select ActiveSheet.Paste Columns("S:S").Select Selection.Cut Columns("G:G").Select ActiveSheet.Paste Columns("T:T").Select Selection.Cut Columns("I:I").Select ActiveSheet.Paste Columns("AB:AB").Select Selection.Cut Columns("L:L").Select ActiveSheet.Paste Range("M6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]*1.5)" Range("M6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 12).Select Range("M7", ActiveCell).Select ActiveSheet.Paste Range("N6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-2]*2)" Range("N6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 13).Select Range("N7", ActiveCell).Select ActiveSheet.Paste Columns("AD:AD").Select Application.CutCopyMode = False Selection.Cut Columns("Q:Q").Select ActiveSheet.Paste Columns("S:AD").Select Selection.Delete Shift:=xlToLeft Columns("Q:Q").Select Selection.Replace What:="CA", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Select Range("E1").Activate Selection.NumberFormat = "@" Sheets("725").Select |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
excel macro missing worksheets
Thought I should add the collection that you need to search for the worksheets.
While I'm here I'll address the question you asked about reducing your code. If you use code like: Range("A1:D4").Copy Range("R12:U15") Instead of: Range("A1:D4").Select Selction.Copy Range("R12:U15").Select Selection.PasteSpecial Paste:=xlValues It does three things at a minimum. It save you time and effort in typing the code. It makes the code more efficient. And it reduces the flicker and flash that you will see on your screen. For Each sh In ActiveWorkbook.Worksheets If sh = Sheets("550") Then 'Insert your code for 550 End If If sh = Sheets("725") Then 'Insert your code for 725 End if Next "JLGWhiz" wrote: Something like this might work: For Each sh In ActiveWorkbook If sh = Sheets("550") Then 'Insert your code for 550 End If If sh = Sheets("725") Then 'Insert your code for 725 End if Next You should try to eliminate some of the selects and activate like the sample below: Sheets("550").Columns("A:R").Select Range("A13").EntireColumn.Hidden = False Range("A13").Delete Shift:=xlToLeft Range("B13").Delete Shift:=xlToLeft Columns("E:E").ColumnWidth = 5.67 " wrote: I have this excel file with multiple worksheets, I created a macro that goes into each worksheet and formats a whole lot of stuff. The problem is that some times the excel file does not have all the worksheets included, when this happens and I try to run the macro, I get errors because of the missing worksheets and the macro does not finish is there a way to get the macro to skip the set of commands if the worksheet is missing, so it would go to the next worksheet and continue the macro? listed below is a piece of the macro, ending with the selection of the next worksheet. Sheets("550").Select Columns("A:R").Select Range("A13").Activate Selection.EntireColumn.Hidden = False Columns("A:A").Select Range("A13").Activate Selection.Delete Shift:=xlToLeft Columns("B:B").Select Range("B13").Activate Selection.Delete Shift:=xlToLeft Columns("E:E").ColumnWidth = 4.56 Columns("E:E").ColumnWidth = 5.67 Columns("E:E").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("V:V").Select Selection.Cut Columns("E:E").Select ActiveSheet.Paste Columns("W:W").Select Selection.Cut Columns("F:F").Select ActiveSheet.Paste Columns("S:S").Select Selection.Cut Columns("G:G").Select ActiveSheet.Paste Columns("T:T").Select Selection.Cut Columns("I:I").Select ActiveSheet.Paste Columns("AB:AB").Select Selection.Cut Columns("L:L").Select ActiveSheet.Paste Range("M6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]*1.5)" Range("M6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 12).Select Range("M7", ActiveCell).Select ActiveSheet.Paste Range("N6").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-2]*2)" Range("N6").Select Selection.Copy Range("A6").Select Cells(Rows.Count, ActiveCell.Column).End(xlUp).Offset(, 13).Select Range("N7", ActiveCell).Select ActiveSheet.Paste Columns("AD:AD").Select Application.CutCopyMode = False Selection.Cut Columns("Q:Q").Select ActiveSheet.Paste Columns("S:AD").Select Selection.Delete Shift:=xlToLeft Columns("Q:Q").Select Selection.Replace What:="CA", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Select Range("E1").Activate Selection.NumberFormat = "@" Sheets("725").Select |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| finding missing data between two worksheets | Excel Discussion (Misc queries) | |||
| missing the personal macro workbook option in excel? | Excel Discussion (Misc queries) | |||
| INDIRECT.EXT problem with missing worksheets | Excel Worksheet Functions | |||
| On Error? Creates 1 missing worksheet then never detects any other missing worksheets | Excel Programming | |||
| objects missing in exixting worksheets | Excel Discussion (Misc queries) | |||
Linear Mode
