VBA question: can't call a range object

[Blackjack200]

Update: Work around found, see below, but if anyone can figure out why this original code wasn't working, I'd still be interested.

First off, here's the code. The commented lines may not make much sense since I've been chopping and stitching.

Sub Macro1()
'
' Macro1 Macro
'

'
    Dim loopCounter As Integer
    Dim DestWorkSheet As Excel.Worksheet
    Dim SourceWorkSheet As Excel.Worksheet
    Dim TodaysDate As String
    Dim SourceFileName As String
    Dim SourceWorkBook As Workbook
    Dim SourceSheetName As String
    Dim DestWorkBook As Workbook
    Dim xlapp As Excel.Application
    
    
    SourceSheetName = "Rpt_CT_Assets"
    SourceFileName = Application.GetOpenFilename
    loopCounter = 1
    Set xlapp = New Excel.Application
     
    With xlapp
    
     .Visible = True
       Set SourceWorkBook = .Workbooks.Open(SourceFileName, , False)
       .WindowState = xlMaximized
       
       Set SourceWorkSheet = SourceWorkBook.Worksheets(SourceSheetName)
       
        Do While loopCounter <= 1
            Set DestWorkBook = .Workbooks.Add
            Set DestWorkSheet = DestWorkBook.Worksheets("Sheet1")
            SourceWorkSheet.Activate
                SourceWorkSheet.Range("A2").AutoFilter
                SourceWorkSheet.Range("A2").AutoFilter Field:=7, Criteria1:="Bank of America, N.A."
        SourceWorkSheet.Rows("1:1").Select
'        SourceWorkSheet.Range("A2").Copy
      SourceWorkSheet.Range(Selection, Selection.End(xlDown)).Select
'        Selection.Copy
'       DestWorkSheet.Activate
'        DestWorkSheet.Paste
'        With Selection.Interior
'         .Pattern = xlNone
'         .TintAndShade = 0
'         .PatternTintAndShade = 0
'        End With
'    With DestWorkSheet
'    Range("A1:D1").Select
'    Selection.UnMerge
'    Columns("A:B").Select
'    Selection.Delete Shift:=xlToLeft
'    Columns("C:C").Select
'    Selection.Delete Shift:=xlToLeft
'    Columns("F:G").Select
'    Selection.Delete Shift:=xlToLeft
'    Columns("G:K").Select
'    Selection.Delete
'    Range("A1:F2").Select
'    With Selection.Interior
'        .Pattern = xlPatternLinearGradient
'        .Gradient.Degree = 90
'        .Gradient.ColorStops.Clear
'        .Gradient.ColorStops.Add (0)
'        .PatternColorIndex = xlAutomatic
'        .Color = 16737792
'        .TintAndShade = 0
'        .PatternTintAndShade = 0
'
'    End With
    
        loopCounter = loopCounter + 1
    Loop
              
       
    End With
    
'    TodaysDate = InputBox("Enter the date as it should appear at the top of the report.")
'
'
'
'    Set DestWorkSheet = Sheets.Add
'    DestWorkSheet.Move After:=Sheets(loopCounter + 1)
'    DestWorkSheet.Select
'    DestWorkSheet.Name = Left(FamilyKey(loopCounter), 31)
'    SourceWorkSheet.Select
'    Rows("2:2").Select
'    Selection.AutoFilter
'    ActiveSheet.Range("$A$2:$P$3638").AutoFilter Field:=7, Criteria1:= _
'        FamilyKey(loopCounter)
'    Rows("3:3").Select
'    Range(Selection, Selection.End(xlDown)).Select
'    Range(Selection, Selection.End(xlUp)).Select
'    Selection.Copy
'    DestWorkSheet.Select
'    DestWorkSheet.Paste
'    With Selection.Interior
'        .Pattern = xlNone
'        .TintAndShade = 0
'        .PatternTintAndShade = 0
'    End With
'
'    Range("A1:D1").Select
'    Selection.UnMerge
'    Columns("A:B").Select
'    Selection.Delete Shift:=xlToLeft
'    Columns("C:C").Select
'    Selection.Delete Shift:=xlToLeft
'    Columns("F:G").Select
'    Selection.Delete Shift:=xlToLeft
'    Columns("G:K").Select
'    Selection.Delete
'    Range("A1:F2").Select
'    With Selection.Interior
'        .Pattern = xlPatternLinearGradient
'        .Gradient.Degree = 90
'        .Gradient.ColorStops.Clear
'        .Gradient.ColorStops.Add (0)
'        .PatternColorIndex = xlAutomatic
'        .Color = 16737792
'        .TintAndShade = 0
'        .PatternTintAndShade = 0
'
'    End With
'
'    Rows("3:3").Select
'    Selection.AutoFilter
'    ActiveSheet.Range("$A$1:$P$3638").AutoFilter Field:=2, Criteria1:="NONRG"
'    Range(Selection, Selection.End(xlDown)).Select
'    Selection.Delete Shift:=xlUp
'    'Selection.AutoFilter
'    Columns("A:A").Select
'    Selection.ColumnWidth = 58.43
'    Columns("B:B").Select
'    Selection.ColumnWidth = 19.29
'    Columns("C:C").Select
'    Selection.ColumnWidth = 23.29
'    Columns("D:D").Select
'    Selection.ColumnWidth = 19
'    Columns("E:E").Select
'    Selection.ColumnWidth = 10.29
'    Columns("F:F").Select
'    Selection.ColumnWidth = 17
'    Rows("3:3").Select
'    Range(Selection, Selection.End(xlDown)).Select
'    Selection.RowHeight = 12.75
'    Rows("2:2").Select
'    Range(Selection, Selection.End(xlDown)).Select
'    Selection.RowHeight = 12.75
'
'
'    loopCounter = loopCounter + 1
'    ActiveSheet.Range("A1") = "Month End Period:  " & TodaysDate
'    ActiveSheet.Range("A2") = SourceFileName
'    Loop
End Sub

The line that throws the error is "SourceWorkSheet.Range(Selection, Selection.End(xlDown)).Select" (the last ucommented line before the loop increment)

The error is: Method "Range" of object "_Worksheet" failed

Which implies that there's an issue with the object "SourceWorkSheet" being able to call .Range.

But if I change the arguments in .Range to something trivial like SourceWorkSheet.Range("A2").Select

the code works fine. So it must be a problem with the arguments Selection, Selection.End(xlDown)

Anyone have any thoughts? All I'm trying to do with the selection is the equivalent of hitting CTRL+SHIFT+DOWN on the keyboard.

Any help is appreciated, this has been driving me nuts.

 

[beginner99]

your selecting a selection. Doesn't make much sense to me.

 

[Blackjack200]

That's how the code is generated when I use the record macro tool.

But if there's a better way, I'm all ears. All I want to do is copy all the filtered cells on a sheet without copying the unfiltered cells.

 

[Blackjack200]

Ok, I found a work around after searching around. If anyone's interested

I declared "rng" as a Range variable:
Dim rng As Range

Then added these two lines of code after the filter code:

Set rng = SourceWorkSheet.AutoFilter.Range
rng.Copy

If anyone cares to explain why my original code was failing, I'd still be interested.