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