Run-time 1004 error on range select

  • Thread starter Thread starter Morgan
  • Start date Start date
M

Morgan

Hello,

I have a command button on Sheet1 that I use to insert a new column o
that sheet, but now need it to also copy column "A" on Sheet2 to th
first blank column on that sheet.

Here is what I have:

Private Sub CommandButton2_Click()

' Insert new column on Sheet1
ActiveCell.Select
Selection.EntireColumn.Insert Shift:=xlToRight

Dim src As Range
Dim dest As Range
Set src = Cells("2", ActiveCell.Column + 1)
Set dest = Cells("2", ActiveCell.Column)
src.Copy dest

' copy column on Sheet2
Sheets("sheet2").Activate
Columns("A").Select
Selection.Copy

Range("E1").End(xlToRight).Select
Selection.EntireColumn.Select
ActiveSheet.Paste
End Sub

But, it seems like no matter how I phrase the range select on Sheet2,
get a run-time 1004 error: select method of range class failed. I'v
also tried:

' copy column on Sheet2
Sheets("sheet2").Activate
Range("A1").Select
Selection.Copy

but still get the same error.

Any suggestions would be appreciated.

Thank you
 
Morgan

Your code appears work ok on my Excel 97

I do suggest some changes

change

' copy column on Sheet2
Sheets("sheet2").Activate
Columns("A").Select
Selection.Copy


to

Sheets("sheet2").Columns("A").Copy ' no need to activate sheet o
column



Change

Range("E1").End(xlToRight).Select
Selection.EntireColumn.Select
ActiveSheet.Paste


to

dim DestCol as Integer
DestCol = Range("E1").End(xlToRight).Column
Sheet("sheet1").Columns(DestCol).Paste
Selection.EntireColumn.Selec
 
Morgan

If you are using XL97 and the command button from the Control Toolbox, make
sure you set the TakeFocusOnClick property of the command button to False.
 
Thank you. I'll go ahead and try out your suggestions right away.

I did notice something that I forgot before. Column A that I a
pasting from has conditional formating, with absolute references t
$A$1. When I paste this column, the conditional formating gets pasted
too.

When I replace...

ActiveSheet.Paste

With...

ActiveSheet.PasteSpecial Paste:=xlFormulas

I get another run-time 1004 error: Application-defined o
object-defined error.

Thanks again
 
Back
Top