Macro code doesn't work with Button

[eighthman11]

Hello everyone: Need some help. I am not an Excel Person, but got
stuck with a project because I was off the day they took the vote on
who should do it.

So far I've been pretty lucky just recording marco's and then placing
the marco code on the click event of a button. Except for the code
below. It runs fine as a macro but bombs when the code is copied to a
button. It's just doing a filter then copying a range of cells from
one worksheet to a new worksheet.

The first line of code that bombs is "Columns("A:E").Select.

Any help would be appreciated.

==================================================================

Dim x As String
Dim y As String

x = ActiveWorkbook.Name
Selection.AutoFilter Field:=6, Criteria1:="<>"
Columns("B:F").Select
Selection.Copy
Workbooks.Add
y = ActiveWorkbook.Name
ActiveSheet.Paste
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Range("A6").Select

Windows(x).Activate
Range("A10").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=6
Windows(y).Activate
Range("A5").Select
End Sub

 

[Jim Cone]

Re: "but bombs when the code is copied to a button."

Are you copying the code to a sheet module?
If so, you must qualify all range references with the sheet the range is on...
Workbooks(y).Columns("A:E").Select

Tips on posting to Excel newsgroups here...
http://www.cpearson.com/excel/newposte.htm
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"eighthman11"
wrote in message
Hello everyone: Need some help. I am not an Excel Person, but got
stuck with a project because I was off the day they took the vote on
who should do it.
So far I've been pretty lucky just recording marco's and then placing
the marco code on the click event of a button. Except for the code
below. It runs fine as a macro but bombs when the code is copied to a
button. It's just doing a filter then copying a range of cells from
one worksheet to a new worksheet.
The first line of code that bombs is "Columns("A:E").Select.
Any help would be appreciated.

==================================================================

Dim x As String
Dim y As String

x = ActiveWorkbook.Name
Selection.AutoFilter Field:=6, Criteria1:="<>"
Columns("B:F").Select
Selection.Copy
Workbooks.Add
y = ActiveWorkbook.Name
ActiveSheet.Paste
Columns("A:E").Select
Columns("A:E").EntireColumn.AutoFit
Range("A6").Select

Windows(x).Activate
Range("A10").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=6
Windows(y).Activate
Range("A5").Select
End Sub