Hi All, this is a weird one. Any help will be appreciated.

I have a macro which works perfectly. It runs from sheet2, make
changes on sheet1 then finishes on bach sheet2. Which is what I want
It works fine when I run it as a macro, however when I copy and past
the EXACT code into a command button and click the button I get a
error!?!?

Here is the code...

Sub temp()
Sheets("sheet1").Select
Range("AF4").Select
Selection.End(xlToLeft).Select
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Copy
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
ActiveSheet.Paste
ActiveCell.Offset(12, 2).Range("A1").Select
Sheets("sheet2").Select
ActiveCell.Select
End Sub

Here is the error...

Runtime error '1004'
Select method of range class failed

the line that is highlighted when I run the debugger is the 2nd line
Range("AF4").Select.


CHEER

--
Message posted from http://www.ExcelForum.com

Put the below in a general module
Sub temp()
Sheets("sheet1").Select
Range("AF4").Select
Selection.End(xlToLeft).Select
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Copy
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
ActiveSheet.Paste
ActiveCell.Offset(12, 2).Range("A1").Select
Sheets("sheet2").Select
ActiveCell.Select
End Sub

Modify your commandbutton code to this:

Private Sub Commandbutton1_Click
Temp
End Sub


The problem is that unqualifed references in a sheet module refer to that
sheet. So Range("AF4").Select refers to the sheet containing the code, but
Sheet1 is active and you can not select on the sheet containing the code.
This behavior is different than in a general module where the unqualified
range refers to the active sheet.
--
Regards,
Tom Ogilvy


"The Grinch " wrote in message
...
Hi All, this is a weird one. Any help will be appreciated.

I have a macro which works perfectly. It runs from sheet2, makes
changes on sheet1 then finishes on bach sheet2. Which is what I want.
It works fine when I run it as a macro, however when I copy and paste
the EXACT code into a command button and click the button I get an
error!?!?

Here is the code...

Sub temp()
Sheets("sheet1").Select
Range("AF4").Select
Selection.End(xlToLeft).Select
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Copy
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
ActiveSheet.Paste
ActiveCell.Offset(12, 2).Range("A1").Select
Sheets("sheet2").Select
ActiveCell.Select
End Sub

Here is the error...

Runtime error '1004'
Select method of range class failed

the line that is highlighted when I run the debugger is the 2nd line:
Range("AF4").Select.


CHEERS


---
Message posted from http://www.ExcelForum.com/

try this
Private Sub CommandButton1_Click()
With Sheets("sheet1")
x = .Range("AF4").End(xlToLeft).Column
.Columns(x).Copy .Columns(x + 1)
End With
End Sub

--
Don Guillett
SalesAid Software

"The Grinch " wrote in message
...
Hi All, this is a weird one. Any help will be appreciated.

I have a macro which works perfectly. It runs from sheet2, makes
changes on sheet1 then finishes on bach sheet2. Which is what I want.
It works fine when I run it as a macro, however when I copy and paste
the EXACT code into a command button and click the button I get an
error!?!?

Here is the code...

Sub temp()
Sheets("sheet1").Select
Range("AF4").Select
Selection.End(xlToLeft).Select
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Copy
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
ActiveSheet.Paste
ActiveCell.Offset(12, 2).Range("A1").Select
Sheets("sheet2").Select
ActiveCell.Select
End Sub

Here is the error...

Runtime error '1004'
Select method of range class failed

the line that is highlighted when I run the debugger is the 2nd line:
Range("AF4").Select.


CHEERS


---
Message posted from http://www.ExcelForum.com/

Thanks guys, im sorted now

--
Message posted from http://www.ExcelForum.com

glad to help

--
Don Guillett
SalesAid Software

"The Grinch " wrote in message
...
Thanks guys, im sorted now!


---
Message posted from http://www.ExcelForum.com/