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

[rjwatsoniii]

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

 

[VoG]

You need to qualify the sheet. Something like

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

 

[Norie]

Rich

Why use it in the first place?

You also don't need this, as you use the full path name when you open the file.

ChDir "J:\Processing\"

Also all that use of ActiveWorkbook/Window/Select etc makes the code hard to follow.

And ActiveWorkbook.Save twice?

 

[rjwatsoniii]

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]

See if this works

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