Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie needs macro help please
I have a file with 2 worksheets Main and Data and want to run a macro
from a Command Button in the Main worksheet which goes to the Data worksheet, refreshes a query, parses the data and goes back to the Main worksheet. This is what I've done: Recorded a macro which is Macro1 in Module1 which works if run from Tools, Macro, Macro1, Run Created a button which gives me Private Sub CommandButton1_Click()in the Objects section for Sheet1 (Main) Copied the code from Module1 Macro1 to the Sub CommandButton1_Click() When I run the macro from the Command Button it gets to Columns("A:A").Select and falls over with an error message "Run time error 1004 - Select method of Range class failed. I don't understand why it doesn't work when linked to the Command Button when it's fine when run straight from Module1 I'd be really grateful if someone could explain whhere I'm going wrong and how to put it right as I've spent hours looking on the net for a solution but I think I'm out of my depth. The code is below Thanks a lot for your help Keith Private Sub CommandButton1_Click() Worksheets("Data").Select Selection.QueryTable.Refresh BackgroundQuery:=False Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:= _ "<", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _ Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1) _ , Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array( _ 19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1)) Range("A1").Select Sheets("Main").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie needs macro help please
first, i wouldn't have copied the whole macro to the
commandbutton code. i would have type in the commandbutton code Private Sub CommandButton1_Click() Call Macro1 end sub but since it is failing at line Columns("A:A").Select try changing it to Range("A1").select Selecting the whole column is selecting cells outside the querytable range. Selecting a single cell within the querytable range allows excel and microsoft query to select the querytable range. just a guess. let me know if it works. -----Original Message----- I have a file with 2 worksheets Main and Data and want to run a macro from a Command Button in the Main worksheet which goes to the Data worksheet, refreshes a query, parses the data and goes back to the Main worksheet. This is what I've done: Recorded a macro which is Macro1 in Module1 which works if run from Tools, Macro, Macro1, Run Created a button which gives me Private Sub CommandButton1_Click()in the Objects section for Sheet1 (Main) Copied the code from Module1 Macro1 to the Sub CommandButton1_Click() When I run the macro from the Command Button it gets to Columns("A:A").Select and falls over with an error message "Run time error 1004 - Select method of Range class failed. I don't understand why it doesn't work when linked to the Command Button when it's fine when run straight from Module1 I'd be really grateful if someone could explain whhere I'm going wrong and how to put it right as I've spent hours looking on the net for a solution but I think I'm out of my depth. The code is below Thanks a lot for your help Keith Private Sub CommandButton1_Click() Worksheets("Data").Select Selection.QueryTable.Refresh BackgroundQuery:=False Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:= _ "<", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _ Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1) _ , Array(13, 1), Array(14, 1), Array(15, 1), Array (16, 1), Array(17, 1), Array(18, 1), Array( _ 19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1)) Range("A1").Select Sheets("Main").Select End Sub . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie needs macro help please
Many thanks for the replies.
Tried both solutions. Unfortunately Anon. I got the same error with your solution. Don's solution copied and pasted under CommandButton1_Click()did the trick though. Thanks again Keith "Keith" wrote: I have a file with 2 worksheets Main and Data and want to run a macro from a Command Button in the Main worksheet which goes to the Data worksheet, refreshes a query, parses the data and goes back to the Main worksheet. This is what I've done: Recorded a macro which is Macro1 in Module1 which works if run from Tools, Macro, Macro1, Run Created a button which gives me Private Sub CommandButton1_Click()in the Objects section for Sheet1 (Main) Copied the code from Module1 Macro1 to the Sub CommandButton1_Click() When I run the macro from the Command Button it gets to Columns("A:A").Select and falls over with an error message "Run time error 1004 - Select method of Range class failed. I don't understand why it doesn't work when linked to the Command Button when it's fine when run straight from Module1 I'd be really grateful if someone could explain whhere I'm going wrong and how to put it right as I've spent hours looking on the net for a solution but I think I'm out of my depth. The code is below Thanks a lot for your help Keith Private Sub CommandButton1_Click() Worksheets("Data").Select Selection.QueryTable.Refresh BackgroundQuery:=False Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:= _ "<", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _ Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1) _ , Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array( _ 19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1)) Range("A1").Select Sheets("Main").Select End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Newbie needs macro help please
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie: VBA? Macro? Please Advise... | Excel Discussion (Misc queries) | |||
Macro newbie | Excel Programming | |||
VBA Newbie - Simple Macro | Excel Programming | |||
Loop macro for a Newbie | Excel Programming | |||
Macro Help for Newbie | Excel Programming |