Run-time error '9': subscript out of range

[marcus.brown]

Hi,

I'm trying to create a macro for a colleague, to run an advanced filter on one of their sheets.

I've cobbled together the code below, but if the workbook isn't open, I get an error (Run-time error '9': Subscript out of range). When i click on debug, it highlights the code below which i've put in bold.

Can you please help?

Thanks!

Dim wb As Workbook
Dim AlreadyOpen As Boolean

AlreadyOpen = False
For Each wb In Workbooks
If wb.Name = "Monitor Record Sheet.xlsm" Then
AlreadyOpen = True
Exit For
End If
Next wb
If AlreadyOpen = False Then
Workbooks.Open Filename:="U:\Record Sheet.xlsm", ReadOnly:=True
End If

Workbooks("Record Sheet.xlsm").Sheets("ALL blue monitoring data"). _
Columns("D:AE").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets( _
"Sheet2").Range("A1:A2"), CopyToRange:=Range("A1"), Unique:=False

 

[Andrew Poulsom]

Maybe your problem is that you haven't qualified Sheet2 with its workbook or Range("A1") with its worksheet. Try:

With Workbooks("Record Sheet.xlsm")
    .Sheets("ALL blue monitoring data").Columns("D:AE").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Sheets("Sheet2").Range("A1:A2"), CopyToRange:=.Sheets("ALL blue monitoring data").Range("A1"), Unique:=False
End With

 

[marcus.brown]

no, it didn't work. I get the same error.

Just to clarify, i'm filtering from 1 workbook (Record Sheet) and pasting in another, my criteria is in the same workbook as to where i'm pasting it to - just in a different sheet.

 

[ChrisM]

Not sure I'm reading this right, but you open and test for:

"Monitor Record Sheet.xlsm"

Yet your last line of macro code uses:

"Record Sheet.xlsm"

shouldn't these be spelled the same?

 

[Andrew Poulsom]

Well Excel can't find something. To see what it is try:

MsgBox Workbooks("Record Sheet.xlsm").Name
MsgBox Workbooks("Record Sheet.xlsm").Sheets("ALL blue monitoring data").Name
MsgBox Sheets("Sheet2").Name