Newbie needs macro help please

[Keith]

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

 

[Guest]

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.

 

[Don Guillett]

Probably has something to do with the take focus of the command button. Try
assigning to a button from the forms menu or to a shape from the drawing
menu to avoid that problem. I alomost always use shapes...

BTW you don't need all those SELECTIONs. from your MAIN sheet try

with Worksheets("Data")
.QueryTables(1).Refresh BackgroundQuery:=False
.Columns("A:A").TextToColumns Destination:=.Range("A1"), _
DataType:=xlDelimited,Comma:=True
end with

 

[Guest]

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

 

[Don Guillett]

Glad it helped. I still don't like command buttons. Try using shapes. You
can be more creative.