excel macro missing worksheets

P

pidoling

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
 
V

Vasant Nanavati

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.
_______________________________________________________________________
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
Click to expand...
 
P

pidoling

Thanks. Well, this is only a piece of the code, you would probably
scream at me if you saw all of it.

The purpose of this macro is to prepare the data so that I can import
the data into this third party database using there import program, so
the data needs to be in the correct order/format. So the macro
formats, reorders all the worksheets, then merges all the worksheets
into one file and then saves it as a .csv file, which will then allow
me to import the data.

One of the issues that I have is that not all the worksheets have the
same layout. How would I benefit if I would reduce the code? I
appreciate the feedback but at this point is it worth changing the
code?

Thanks once again for the answer.
 
J

Joel

you can use a select case (see below)


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

[email protected] said:
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
Click to expand...
 
G

Gary Brown

Advice:
Put all that formatting in another procedure in your module that you call
instead of putting the same code in the procedure over and over again.

ie:
Sub FormatMe
'all your formatting
End Sub

Then in the main proedure simply have...
If WorksheetExists("550") = True then
Call FormatMe
End If

If WorksheetExists("724") = True then
Call FormatMe
End If

etc.

Much nicer, no?

--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.



[email protected] said:
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
Click to expand...
 
G

Gary Brown

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
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.



[email protected] said:
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
Click to expand...
 
J

Joel

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

[email protected] said:
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
Click to expand...
 
P

p45cal

Several references to help you along:
http://www.dailydoseofexcel.com/archives/2004/05/18/sheetexists/
http://j-walk.com/ss//excel/tips/tip54.htm#func5
http://saltwetfish.wordpress.com/2007/03/16/excel-sheetexists/
http://xlvba.3.forumer.com/index.php?s=a1d45a76190f08b7ca52f41a5b7a872e&showtopic=235


--
p45cal


[email protected] said:
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
Click to expand...
 
J

JLGWhiz

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



[email protected] said:
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
Click to expand...
 
J

JLGWhiz

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 said:
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



[email protected] said:
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
Click to expand...
Click to expand...
 

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

Similar Threads

Select method failed - Error 400 2
Adding Columns in Excel 2007 (2003 Compatibility Mode) 2
variable number of rows. 3
Complie Syntax Error 6
Macro Loop 1
macro 2
How can I indicate in a macro to which last sheet to return ? 9
Macro: insert rows anywhere in the sheet 4

Top