Runtime error 1004

[Guest]

I can run the following code from the command button but get
"Runtime error "1004"
Application-defined or object-defined error" when I try to step thru it in
VBA at Range("J2").Select

Private Sub CommandButton1_Click() 'Print or view birthday list
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'
Dim Birthdate As String

Birthdate = Range("Form!M15")
Sheets("Records").Select
Range("J2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:=Birthdate, Operator:=xlAnd
Selection.Sort Key1:=Range("J3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("B2:L2").Select ' Selects all filtered data
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy 'Copies filtered job data
Sheets("Birthday").Select
Range("A2").Select
ActiveSheet.Paste 'Pastes Values

Range("A1:K2").Select ' Selects all filtered data
Range(Selection, Selection.End(xlDown)).Select
'Selection.Copy 'Copies filtered job
Selection.PrintOut Copies:=1, Preview:=True, Collate:=True
Range("A2:K2").Select ' Selects all filtered data
Range(Selection, Selection.End(xlDown)).Select

Selection.Clear
Sheets("Records").Select
Selection.AutoFilter
Sheets("Form").Select ' Goes to date input screen
Range("A47").Select
Range("C47").Select
End SubI have the following code

 

[Chip Pearson]

Are you using Excel 97? If so, you need to change the
SetFocusOnClick property of the command button to FALSE.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

I am using 2003. It also fails 2000

Are you using Excel 97? If so, you need to change the
SetFocusOnClick property of the command button to FALSE.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

I have had similar problems in the past, try fully qualifying the range name.
e.g.

Worksheets("Sheet1").Range("J2").Select

Replace sheet1 with your sheet name.

 

[Dave Peterson]

When you have an unqualified range in a general module, it will refer to the
activesheet.

When you have an unqualified range in a worksheet module, it will refer to the
sheet holding the code.

So this:

sheets("Records").select
range("J2").select

is the same as:
sheets("Records").select
sheets("form").range("J2").select
(The button is on the Form sheet???)

Since you can't select a cell on a non-active worksheet, your code blows up.

But you can do lots of things without selecting. I'm not sure how your data is
laid out and it's kind of difficult to see from just the code, but this may get
you started in another direction:

Option Explicit
Private Sub CommandButton1_Click() 'Print or view birthday list
'date or string???
Dim Birthdate As Date
Dim LastRow As Long
Dim RngToCopy As Range

Application.ScreenUpdating = False

Birthdate = Worksheets("form").Range("M15").Value

With Sheets("Records")
.AutoFilterMode = False
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A1:L" & LastRow)
.AutoFilter Field:=10, Criteria1:=CLng(Birthdate)
.Sort Key1:=.Columns(10), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
If .AutoFilter.Range.Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
.Count = 1 Then
'nothing filtered, what should happen
MsgBox "nothing to copy"
GoTo ExitNow:
Else
With .AutoFilter.Range
Set RngToCopy = .Resize(.Rows.Count - 1).Offset(1, 0)
End With
End If
End With

With Sheets("birthday")
RngToCopy.Copy _
Destination:=.Range("a2")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("A1:K" & LastRow)
.PrintOut Copies:=1, Preview:=True, Collate:=True
.ClearContents
End With
End With

ExitNow:
Application.ScreenUpdating = True

End Sub

I used column A to determine the last row of each worksheet. If you can't use
column A, then modify those portions.

And I wasn't quite sure what was going on at the bottom of your code.

This did compile, but I didn't test it.

 

[Guest]

Thanks

When you have an unqualified range in a general module, it will refer to the
activesheet.

When you have an unqualified range in a worksheet module, it will refer to the
sheet holding the code.

So this:

sheets("Records").select
range("J2").select

is the same as:
sheets("Records").select
sheets("form").range("J2").select
(The button is on the Form sheet???)

Since you can't select a cell on a non-active worksheet, your code blows up.

But you can do lots of things without selecting. I'm not sure how your data is
laid out and it's kind of difficult to see from just the code, but this may get
you started in another direction:

Option Explicit
Private Sub CommandButton1_Click() 'Print or view birthday list
'date or string???
Dim Birthdate As Date
Dim LastRow As Long
Dim RngToCopy As Range

Application.ScreenUpdating = False

Birthdate = Worksheets("form").Range("M15").Value

With Sheets("Records")
.AutoFilterMode = False
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("A1:L" & LastRow)
.AutoFilter Field:=10, Criteria1:=CLng(Birthdate)
.Sort Key1:=.Columns(10), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
If .AutoFilter.Range.Columns(1).Cells.SpecialCells(xlCellTypeVisible) _
.Count = 1 Then
'nothing filtered, what should happen
MsgBox "nothing to copy"
GoTo ExitNow:
Else
With .AutoFilter.Range
Set RngToCopy = .Resize(.Rows.Count - 1).Offset(1, 0)
End With
End If
End With

With Sheets("birthday")
RngToCopy.Copy _
Destination:=.Range("a2")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

With .Range("A1:K" & LastRow)
.PrintOut Copies:=1, Preview:=True, Collate:=True
.ClearContents
End With
End With

ExitNow:
Application.ScreenUpdating = True

End Sub

I used column A to determine the last row of each worksheet. If you can't use
column A, then modify those portions.

And I wasn't quite sure what was going on at the bottom of your code.

This did compile, but I didn't test it.