Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select Method of Range Class Failed (2000)

    Below is the code I am running to look at all of the rows of a worksheet and, if the conditions are met,copy the row onto Sheet2. After that I want to Sort and then Subtotal. The problem is that when the macro gets to the "Cells.Select" line 10 rows from the bottom it returns the "Select method of Range class failed."
    What am I doing wrong in trying to select all of the cells in this sheet?!?!

    Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Sheets("Data Entry").Activate
    Dim rng As Range
    Dim lngRow As Long
    Set rng = Range("A1", Range("A65536").End(xlUp))
    For lngRow = rng.Rows.Count To 2 Step -1
    If rng(lngRow) <> "" And rng(lngRow) <> "L. NAME" And rng(lngRow) <> "SUBTOTAL" Then
    rng(lngRow).EntireRow.Select
    Selection.Copy
    Sheets("Sheet2").Select
    Sheets("Sheet2").Range("a65536").End(xlUp).Offset( 1, 0).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Sheets("Data Entry").Select
    End If
    Next lngRow
    Sheets("Sheet2").Select
    Cells.Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("A2") _
    , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
    , Orientation:=xlTopToBottom
    Cells.Select
    Selection.Subtotal GroupBy:=4, Function:=xlSum, TotalList:=Array(6, 7, 8, 10 _
    , 11, 12, 13, 14, 15, 16, 17, 18, 19), Replace:=True, PageBreaks:=False, _
    SummaryBelowData:=True
    Application.ScreenUpdating = True
    End Sub


    Thanks for any help you can give.

    Stats

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Method of Range Class Failed (2000)

    When I test this, it balks at the Subtotal.

    Your code is rather inefficient, because it keeps on selecting ranges and switching between worksheets. Most code in Excel works better if you use ranges instead of selections. Here is a modified version of your code that avoids using the Selection object:

    Private Sub CommandButton1_Click()
    Dim shtSource As Worksheet
    Dim shtTarget As Worksheet
    Dim rng As Range
    Dim lngRow As Long
    Set shtSource = Worksheets("Data Entry")
    Set shtTarget = Worksheets("Sheet2")
    Set rng = shtSource.Range(shtSource.Range("A1"), shtSource.Range("A65536").End(xlUp))
    For lngRow = rng.Rows.Count To 2 Step -1
    If rng(lngRow) <> "" And rng(lngRow) <> "L. NAME" And rng(lngRow) <> "SUBTOTAL" Then
    rng(lngRow).EntireRow.Copy
    shtTarget.Range("a65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlValues
    End If
    Next lngRow
    With shtTarget.Range("A1").CurrentRegion
    .Sort Key1:=Range("D2"), Key2:=Range("A2"), Header:=xlYes, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom
    .Subtotal GroupBy:=4, Function:=xlSum, _
    TotalList:=Array(6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19), Replace:=True
    End With
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •