Hello,
This is my first attempt at writing VBA I'm trying to write some code
to delete some columns in a worksheet but I'm getting a run-time error
9. I tried to use the Excel macro to record a macro to delete and
format the worksheets but the code failed on
"Sheets("MovedOut").Select". I have tried to determine why I received
the error but was not able to.

Any help is appreciated with my code structure is appreciated.


Dim iSheetCount As Integer
Dim iSheet As Integer
Dim ws As Worksheets
Dim wb As Workbook

'Formatting MovedOut Orders Worksheet

Sheets("MovedOut").Select
ActiveCell.Offset(0, 2).Columns("A:D").EntireColumn.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(*
""-""_);_(@_)"
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[84]C)"
ActiveCell.Offset(0, 5).Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "m/d/yyyy"
ActiveWindow.SmallScroll ToRight:=3
ActiveCell.Offset(0, 1).Columns("A:D").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "m/d/yyyy"
ActiveWindow.SmallScroll ToRight:=6
ActiveCell.Offset(0, 1).Columns("A:O").EntireColumn.Select
ActiveWindow.SmallScroll ToRight:=3
ActiveCell.Offset(0, 15).Columns("A:A").EntireColumn.Select
ActiveWindow.LargeScroll ToRight:=-1
ActiveCell.Offset(0, -15).Columns("A:P").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(0, 2).Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "m/d/yyyy"
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.Offset(0, 4).Columns("A:A").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Columns("A:D").EntireColumn.Select
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(*
""-""_);_(@_)"
ActiveCell.Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[84]C)"
Selection.AutoFill Destination:=ActiveCell.Range("A1:D1"), Type:=
_
xlFillDefault
ActiveCell.Range("A1:D1").Select
ActiveCell.Offset(0, 4).Columns("A:A").EntireColumn.Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
ActiveCell.Select

On Error Resume Next


ws("FieldCycleTimes").Select
Selection.AutoFilter Field:=7, Criteria1:="Mr James Favaron"


ws("ForecastNextMonth").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

ws("FieldPipeline").Select
Selection.AutoFilter Field:=5, Criteria1:="Mr James Favaron"

ws("MovedOut").Select
Selection.AutoFilter Field:=5, Criteria1:="Mr James Favaron"

ws("MovedIn").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

ws("Forecast").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

ws("Actual").Select
Selection.AutoFilter Field:=9, Criteria1:="Mr James Favaron"

iSheetCount = ActiveWorkbook.Worksheets.Count
For iSheet = 1 To iSheetCount
Worksheets(iSheet).Activate
MsgBox Worksheets(iSheet).Name
Next iSheet


End Sub


--
Lizz45ie
------------------------------------------------------------------------
Lizz45ie's Profile: http://www.excelforum.com/member.php...o&userid=23410
View this thread: http://www.excelforum.com/showthread...hreadid=479597