macro code doesnt work in command button

  • Thread starter Thread starter The Grinch
  • Start date Start date
T

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
 
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.
 
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
 
Back
Top