Run-time 1004 error on range select

[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

 

[mudraker]

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

 

[Dick Kusleika]

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.

 

[Morgan]

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