macro code doesnt work in command button

[The Grinch]

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

 

[Tom Ogilvy]

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.

 

[Don Guillett]

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

 

[The Grinch]

Thanks guys, im sorted now

 

[Don Guillett]

glad to help