Combining code from cmdClicks into 1 macro

[epic60sman]

Hi Again:
I'm using XL97-SR2. I have 3 sets of code associated with 3 cmdClick buttons that I want to combine into 1 set and 1 button so the user only has to click one button:
BUTTON1 - Refreshes a query & returns data to Columns A:E;
BUTTON2 - Clears F3:G6500 & copies the equations in F2:G2 down the length of the query Table (cols A:E);
BUTTON3 - Refreshes a PT which gets its data from the query Table plus columns F & G.
All 3 Buttons operate, independently, as they should. First, I try to combine 1&2 into cmdClick1 (The indented code is from Click2):

Private Sub CommandButton1_Click()
Dim LastRow As Long
Worksheets("All").Range("A3:E5000").ClearContents
Worksheets("All").Range("A1:A1").QueryTable.Refres h
Range("F3:F3").Select

****LastRow = Application.CountA(ActiveSheet.Range("D"))
****Range("F3:G6500").ClearContents
****Range("F2:G2").Select
****Selection.AutoFill Destination:=Range("F2:G" & LastRow)
****Range("K6:K6").Select
End Sub
IT NOW WILL NOT RUN -- It stops at the "Selection.AutoFill" line with a nondescript error message. I think what is happening is that the query completes but it takes a few seconds for the data to appear in the WS & the error message appears before this happens. So, I tried inserting;
For I= 1 to 100
DoEvents
Next i
after the "Range("F3:F3").Select" but it did not help. SO,THIS IS MY 1st PROBLEM.

Next, I tried moving the CommandButton3 code into CommandButton2 code as follows:

Private Sub CommandButton2_Click()
Dim LastRow As Long
LastRow = Application.CountA(ActiveSheet.Range("D"))
Range("F3:G6500").ClearContents
Range("F2:G2").Select
Selection.AutoFill Destination:=Range("F2:G" & LastRow)
Range("K6:K6").Select
****'Private Sub CommandButton3_Click()
****Set pvtTable=Worksheets("Sheet1").Range("k6").PivotTab le
****pvtTable.RefreshTable
****Range("H1").Value = Now()
End Sub

It balks again, this time at the "pvtTable.RefreshTable" line with another nondescript error message.
This is got me hog-tied. Any help will be appreciated.
I reported similar problems in Post#: 43267.
Regards and End Sub,
Stephen
<img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

[Andrew Cronnolly]

Stephen,

It is difficult to figure what your problem might be, without knowing what happens when your query table is refreshed. Try putting Dubug.Print LastRow in after each assignment of value to it, to see if takes on the expected value.

What is the significance of Range("F3:F3").Select ?

Would an upload of the workbook be feasible ?.

Andrew

[epic60sman]

Andrew:
Range F3 is the starting point of the range I clear in order to fill down with the equations in F2:G2. I have attached the file. I had to reduce the size of the table returned by the query to make the size limit on attachments but it is rarely more than 500 rows anyway.
I added 2 more buttons:
Code for CommandButton4 shows how I tried to insert code from CommandButton2 into code for CommandButton1.
Code for CommandButton5 shows how I tried to insert code from CommandButton3 into code for CommandButton2.
Neither worked.
Thanks for looking at this for me.
Regards,
Stephen

[Andrew Cronnolly]

Hello stephen,

As I don't have the source data and am not that familiar with the Query tool, I remarked out the first clear contents statements (so that the data would remain), and ran the following code without getting an error.

Private Sub CommandButton1_Click()
Dim LastRow As Long
'Worksheets("All").Range("A3:E5000").ClearContents
Worksheets("All").Range("A1:A1").QueryTable.Refres h
LastRow = Application.CountA(ActiveSheet.Range("D"))
Debug.Print LastRow
Range("F3:F3").Select
Range("F3:G6500").ClearContents
Range("F2:G2").Select
Selection.AutoFill Destination:=Range("F2:G" & LastRow)
Range("K6:K6").Select
Set pvtTable = Worksheets(1).Range("k6").PivotTable
pvtTable.RefreshTable
Range("H1").Value = Now()
End Sub

Note Set pvtTable = Worksheets(1).Range("k6").PivotTable. The tab name for the sheet is All, but is referred to "Sheet1" in Private Sub CommandButton3_Click(). I used the Index number, but could have used "All". Would that have been a problem with your code ?.

Hope it helps

[epic60sman]

Andrew:
I was just about to post again. I had concluded 5 minutes ago that the clear contents thing was causing some problems, at least with the code that I was using as a workaround. What I do not get, though, is what happens with the
"Worksheets("All").Range("A1:A1").QueryTable.Refre sh" code (I do not see that you commented it out)? It can't run because you do not have the connection to our database. Reason I ask is that I had concluded that XL goes on with the VBA code without waiting for the query to return the data to the WS. I pretty much know that for sure now. It may wait till the query is complete but not until the data are rerturned. So, if you do not clear the data first, the value for "LastRow" will be determined by the number of rows in the existing table (before the new qury completes). This is one of the reasons I got into this mess in the first place.
I need something, like maybe the "Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)" thing to cue off but I do not know how to use it.
If you would please take a look at the code below and let me know if you know of some way to do this.
Private Sub CommandButton4_Click()
Dim LastRow As Long
'Worksheets("All").Range("A3:E5000").ClearContents
Worksheets("All").Range("A1:A1").QueryTable.Refres h
'newHour = Hour(Now())
'newMinute = Minute(Now()) + 5
'newSecond = Second(Now()) + 10
'waitTime = TimeSerial(newHour, newMinute, newSecond)
'Application.Wait waitTime
Set currCell = Worksheets("All").Range("A2:A2")
Do ' Outer loop
Do While Not IsEmpty(currentCell) ' Inner loop.
Set nextCell = currentCell.Offset(1, 0)
If IsEmpty(nextCell) Then Exit Do
Set currCell = nextCell
Loop
Loop Until Not IsEmpty(currentCell) ' Exit outer loop immediately.
Worksheets("All").Range("R1:R1").Calculate
'LastRow = Application.CountA(ActiveSheet.Range("D"))
LastRow = ActiveSheet.Range("R1:R1")
Range("F3:G6500").ClearContents
Range("F2:G2").Select
Selection.AutoFill Destination:=Range("F2:G" & LastRow)
Range("K6:K6").Select
Worksheets("All").UsedRange.Columns("F:G").Calcula te
End Sub
I tried the Do Loops first and then the Application.Wait waitTime but XL just jumps into the waittime before the data returns and 5-minutes goes by and it picks up where it left off, like nowhere.
To use
Application.OnTime Now + TimeValue("00:05:00"), "my_Procedure"
does "my_Procedure" refer to another sub or can I use it in some way within the existing macro?
Hope I have been clear enough; I rattled this off fast because I wanted to try and get this done before going home tonight.
Thanks
PS
thanks for getting the PT refresh thing to work.

[epic60sman]

Andrew:
OK forget all that in the last post (or most of it anyway). Here is the essence of it: I think that the external query must be run as a separate thing and when it returns the data, then the user can go on to set off other macros.
Here is what I did:
Private Sub CommandButton4_Click()
'Dim LastRow As Long
'Worksheets("All").Range("A3:E5000").ClearContents
Worksheets("All").Range("A1:A1").QueryTable.Refres h
Application.OnTime Now + TimeValue("00:03:00"), "wait3andFillDown"
End Sub

Sub wait3andfillDown()
Worksheets("All").Range("R1:R1").Calculate
LastRow = ActiveSheet.Range("R1:R1")
Range("F3:G6500").ClearContents
Range("F2:G2").Select
Selection.AutoFill Destination:=Range("F2:G" & LastRow)
Range("K6:K6").Select
Worksheets("All").UsedRange.Columns("F:G").Calcula te
End Sub
IT WENT RIGHT INTO THE Application.OnTime THING, TRANSFERED TO THE OTHER SUB AND IT ALL WAITED 3 MINUTES AND THEN STOPPED AT "Selection.AutoFill Destination:=Range("F2:G" & LastRow)" BECAUSE THE VALUE OF CELL R1 (which has the =counta(D) is 2 (because the sub cleared F3:G6500).
So, what to do????
What do you think?
Have a nice weekend.
Regards,
Stephen

[Andrew Cronnolly]

Stephen, I snipped the following from VBA help, and wonder if it might be of some significance to your problem. If your query is SQL, it seems you might be able to hold control until the data has been returned.

<font color=448800>expression.Refresh(BackgroundQuery)

expression Required. An expression that returns a Chart, PivotCache, or QueryTable object. For the PivotCache object, the cache must have at least one PivotTable report associated with it.

BackgroundQuery Optional Variant. Used only with query tables based on the results of an SQL query. True to return control to the procedure as soon as a database connection is made and the the query is submitted (the query is updated in the background). False to return control to the procedure only after all data has been fetched to the worksheet. If this argument isn

[epic60sman]

By God, I think you've got it. I saw this before but it never dawned on me what it was for. I was too emersed in trying different approaches. I am so convinced that this will work that I am going home without even trying it.
Well, I have flushed another 5 hours down the <img src=/S/toilet.gif border=0 alt=toilet width=24 height=26> but I have something to look forward to on Monday.
Thanks and <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>,
Stephen