Macro code doesn't work with Button
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 |
Macro code doesn't work with Button
"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 |
All times are GMT +1. The time now is 01:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com