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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

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.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Morgan" wrote in message
...

Hello,

I have a command button on Sheet1 that I use to insert a new column on
that sheet, but now need it to also copy column "A" on Sheet2 to the
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, I
get a run-time 1004 error: select method of range class failed. I've
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.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com