Error Run-time error '1004': select method of range class failed "cells select"

rjwatsoniii

New Member
Joined
Jan 27, 2009
Messages
2
When I click a button my macro errors out and indicates an issue with the line: "Cells.Select" below:

Any ideas:


Private Sub CommandButton1_Click()
'
Range("D7:D19").Select
Selection.Copy
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ChDir "J:\Processing\"
Workbooks.Open Filename:="J:\Processing\_testamy\Amys CETO DOWNLD.xls"
Cells.Select
Application.CutCopyMode = False
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWorkbook.Save
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

This is Excel 2003 SP3 on Windows XP SP2.

Thank you.

Rich
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
You need to qualify the sheet. Something like

Code:
ActiveSheet.Cells.Select
'
'or
'
Sheets("Sheet1").Cells.Select
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,466
Office Version
365
Platform
Windows
Rich

Why use it in the first place?:eek:

You also don't need this, as you use the full path name when you open the file.
Code:
ChDir "J:\Processing\"
Also all that use of ActiveWorkbook/Window/Select etc makes the code hard to follow.:)

And ActiveWorkbook.Save twice?:unsure:
 

rjwatsoniii

New Member
Joined
Jan 27, 2009
Messages
2
Thank you both for the fixes. Activesheet did the trick.

I just inherited the problem, I didn't create the macro. I am a systems guy not programming/macro dude.

Rich
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
See if this works

Code:
Private Sub CommandButton1_Click()
With Range("D7:D19")
    .Value = .Value
End With
Workbooks.Open Filename:="J:\Processing\_testamy\Amys CETO DOWNLD.xls"
ActiveSheet.Cells.QueryTable.Refresh BackgroundQuery:=False
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWorkbook.Save
ActiveSheet.PrintOut Copies:=3, Collate:=True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,089,901
Messages
5,411,116
Members
403,342
Latest member
faizanhamied

This Week's Hot Topics

Top