Run-time error "1004" Select method of range class failed

[Guest]

Hello,

I'm having a little trouble with a command button I've been playing around
with, all I want it to do is copy text onto another sheet to specific
locations using the destination formatting. I came up with this macro:

Macro1 Macro
' Macro recorded 06/03/2007 by
'

'
ActiveWindow.SmallScroll ToRight:=-2
Range("A3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Sheet1").Select
Range("F3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A7").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub


which works fine, but when I add this code to a command button object in the
sheet I get an error. "Run-time error "1004" Select method of range class
failed"

The error is with these commands...

Sheets("Sheet2").Select
Range("A5").Select

Can anyone help me out?

 

[Don Guillett]

Macro CopyCellstoSheet2
sheets("sheet1").Range("A3").Copy Sheets("Sheet2").Range("A5")
Sheets("Sheet1").Range("F3").Copy Sheets("Sheet2").Range("A7")
End Sub

or , Notice the placement of the . before range

with sheets("sheet1")
.Range("A3").Copy Sheets("Sheet2").Range("A5")
.Range("F3").Copy Sheets("Sheet2").Range("A7")
end with

 

[Guest]

In order to make your code work, you need to replace lines like this

Sheets("Sheet2").Select

With this

Sheets("Sheet2").Activate

But you don't really need to activate sheets and/or select cells when
copying. Per your description, all you want to do is copy text onto another
sheet to specific locations using the destination formatting. The code below
copies the formula in Sheet1!A1 to Sheet2!A1 and retains the format in
Sheet2!A1.

Sheets("Sheet2").Range("A1").Formula = Sheets("Sheet1").Range("A1").Formula

 

[Gord Dibben]

Do you have a "Sheet2" ?

Maybe misspelled or has a space in the name?

Works for me without error if I have a Sheet2.


Gord Dibben MS Excel MVP