Run-time error 9 (Subscript out of range)

[Lizz45ie]

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

[JakeyC]

Don't mean to be patronising, but is your sheet really called
"MovedOut"?
Not "Moved Out" or Moveout"?

[Jim Thomlinson]

hopefully this will give you a little help... It avoids the activecell and
the offsets you are using which for the most part you really don't need.

with Sheets("MovedOut")
.Select 'this is optional butnecessary if you want to use the active cell
.Columns("A:D").EntireColumn.Delete Shift:=xlToLeft
.Columns("A:A").EntireColumn.NumberFormat = "_(* #,##0_);_(* (#,##0);_(*
""-""_);_(@_)"
end with


--
HTH...

Jim Thomlinson


"Lizz45ie" wrote:

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

[George Nicholson]

If you have more than one workbook open, the following may prevent the
subscript out of range error:
(assumes MovedOut is in the same workbook as the code)

Set wb = ThisWorkbook
wb.Sheets("MovedOut")

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Lizz45ie" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>