Macro code doesn't work with Button

E

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
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

For Each WS .... 2
Help me4 2
Different Results from the Same Macro 3
Selection.AutoFilter Issue 1
Paste failing after cut 8
Permutations andText to column problem 6
Help merging two VBA codes 2
Condense code 2

Top