Error 1004 Select method of range class failed

jams

New member
Joined
Jun 14, 2013
Messages
9
Reaction score
0
Points
0
Hi, I have this code that I am trying to generate a report
Code:
rivate Sub Cmdprintpreview_Click()
 
    Dim GetDate
    Dim strMonth, strYear
    Dim ws As Worksheet
 
   Set ws = Sheets("Returns")
   
    'get values from comboboxes
    strMonth = Me.Cbomonth.Value
    strYear = Me.Cboyear.Value
    
    
    GetDate = strMonth & "/" & strYear
    
    
    ws.Range("Period").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=3, Criteria1:=GetDate
    
    Unload Me
    ActiveSheet.PrintPreview    
    Selection.AutoFilter   
    FrmReport.Show
I am getting this error can anyone help thanks
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
IF you push DEBUG when the error comes up, what line is highlighted?

If it's the ews.Range("Period").Select line, then you need to activate the sheet first.

with ews
.activate
.range("Period").Select
end with
 

jams

New member
Joined
Jun 14, 2013
Messages
9
Reaction score
0
Points
0
It is highlighting this line ws .Range("Period").Select
 

jams

New member
Joined
Jun 14, 2013
Messages
9
Reaction score
0
Points
0
It didn't I'm getting a compilation error
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
220
Reaction score
0
Points
16
Location
UK
Excel Version(s)
2016
How about (air code)
Code:
Private Sub Cmdprintpreview_Click()
     Dim GetDate
    Dim strMonth, strYear
    Dim ws As Worksheet
    Set ws = Sheets("Returns")
       'get values from comboboxes
    strMonth = Me.Cbomonth.Value
    strYear = Me.Cboyear.Value
            GetDate = strMonth & "/" & strYear
with ws.Range("Period")
.AutoFilter
.AutoFilter Field:=3, Criteria1:=GetDate
        Unload Me
ws.PrintPreview
.AutoFilter
end with
FrmReport.Show
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
My mistake, that should have been:
Code:
with ws
     .activate
     .range("Period").Select
end with

I accidentally typed an e in front of ws.

And JoePublic makes an excellent point that you don't always have to select something to do something to it. Although you do have to select a worksheet if you then want to select a range within that sheet.
 

jams

New member
Joined
Jun 14, 2013
Messages
9
Reaction score
0
Points
0
I realized the mistake I am still getting the runtime error on this line Selection.AutoFilter

thanks
 

jams

New member
Joined
Jun 14, 2013
Messages
9
Reaction score
0
Points
0
Thanks Joe

I tried the code but is receiving the runtime error on this line .AutoFilter
 
Last edited:

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
it is referring to one column.

That's the problem, I suspect. The Autofilter is trying to filter column 3 of this range as per the below:
Code:
ws.Range("Period").Select     Selection.AutoFilter     Selection.AutoFilter Field:=3, Criteria1:=GetDate

...but there IS no column 3, because "Period" is just one column.

To fix, you probably either need to replace "Period" with a larger range (and adjust the Field argument so that it references the Period column) or you need to change Field:=3 to Field:=1
 

jams

New member
Joined
Jun 14, 2013
Messages
9
Reaction score
0
Points
0
Thanks I replaced "Period" with "C1" and it works
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Cool. Note that it's much safer to refer to named ranges than cell addresses, because named ranges are resistant to row or column deletions/inserts. Whereas if you use cell addresses then if someone adds or deletes rows or columns to the left or above of your cell address, then the code will no longer reference the correct cell. (VBA will still be pointing to the hard-coded "C1", whereas if you were to say add another column then your actual data would be in D1.)
 

jams

New member
Joined
Jun 14, 2013
Messages
9
Reaction score
0
Points
0
thanks i agree that is why I used the named ranged instead of the cell. Can you have a look at the code and tweek it using the named range

thanks
 

JeffreyWeir

Super Moderator
Staff member
Joined
Mar 22, 2011
Messages
357
Reaction score
0
Points
0
Location
New Zealand
Hi Jams. As per my above comment, you need to change Field:=3 to Field:=1

That should do it.
 
Top