Skip to Content
0
Former Member
Nov 29, 2006 at 12:07 AM

Help need VB macro -refresh two queries in a same workbook consecutively ?

70 Views

Hi BW gurus

I need a help from you all regarding VB macros .

In my workbook I have three worksheets.

1. CURRENT MONTH(This worksheet contains output from query 1)

2. PAST 12 MONTHS(This worksheet contains output from query 2)

3. CONSOLIDATED DATA(This worksheet pulls the data from CURRENT MONTH worksheet and PAST 12 MONTHS worksheet)

Now the user requirement is ,

The worksheet CONSOLIDATED DATA should be always active.Being in this worksheet if the user click the refresh button on the Bex toolbar,

The query 1 in the worksheet CURRENT MONTH should be refreshed.

(Query 1 has two selection variables ,Variable 1 is CURRENT MONTH, Variable 2 is PROJECT TYPE)

After clicking execute button by making the variable selection, then the query 2 in the worksheet PAST 12 MONTHS should be refreshed.

(Query 2 has one selection variable)

Variable 1 is PROJECT TYPE which is same as the Variable 2 in Query 1.So user wanna choose only in the first selection screen and the second selection screen of query 2 should be automatically taken care and when we click execute all the refreshed data in worksheet CURRENT MONTH and worksheet PAST 12 MONTHS,should be refreshed in worksheet CONSOLIDATED DATA.

This is how I have written the code. But query 1 selection window appears continuously after clicking execute without showing the query 2 selection window.I know I am going wrong somewhere in my code but I don’t know where I am going wrong and what change I have to make in the code.

<b>Sub SAPBEXonRefresh(queryID As String, resultArea As Range)

'Refresh query1

Set r = Range("SAPBEXqueries!SAPBEXq0001")

Run "sapbex.xla!SAPBEXrefresh", False, r

'Copy the occupied rows from the CURRENT MONTH worksheet to CONSOLIDATED DATA worksheet

Application.DisplayAlerts = False

TargetSheet = ActiveSheet.Name

Sheets("CURRENT MONTH").Activate

j = 19

For i = 31 To 65536

If Range("A" & i).Value = "" Then

'MsgBox "The Cell is Empty"

Else

a = Range("A" & i).Value

b = Range("B" & i).Value

Sheets("CONSOLIDATED DATA").Range("A" & j).Value = a

Sheets("CONSOLIDATED DATA").Range("B" & j).Value = b

j = j + 1

End If

Next

Sheets("CONSOLIDATED DATA").Activate

MsgBox "Finished1"

'Refresh query2

Set r = Range("SAPBEXqueries!SAPBEXq0002")

Run "sapbex.xla!SAPBEXrefresh", False, r

'Copy the occupied rows from PAST 12 MONTHS WORKSHEET to CONSOLIDATED DATA worksheet

Application.DisplayAlerts = False

TargetSheet = ActiveSheet.Name

Sheets("PAST 12 MONTHS").Activate

j1 = 25

For i1 = 31 To 65536

If Range("F" & i1).Value = "" Then

'MsgBox "The Cell is Empty"

Else

a1 = Range("A" & i1).Value

b1 = Range("B" & i1).Value

c1 = Range("C" & i1).Value

d1 = Range("D" & i1).Value

e1 = Range("E" & i1).Value

f1 = Range("F" & i1).Value

g1 = Range("G" & i1).Value

'Sheets("CONSOLIDATED DATA").Range("C" & j1).Value = a1

'Sheets("CONSOLIDATED DATA").Range("D" & j1).Value = b1

Sheets("CONSOLIDATED DATA").Range("F" & j1).Value = a1

Sheets("CONSOLIDATED DATA").Range("G" & j1).Value = b1

Sheets("CONSOLIDATED DATA").Range("H" & j1).Value = c1

Sheets("CONSOLIDATED DATA").Range("I" & j1).Value = d1

Sheets("CONSOLIDATED DATA").Range("J" & j1).Value = e1

Sheets("CONSOLIDATED DATA").Range("K" & j1).Value = f1

Sheets("CONSOLIDATED DATA").Range("L" & j1).Value = g1

j1 = j1 + 1

End If

Next

Sheets("CONSOLIDATED DATA").Activate

MsgBox "Finished2"

End Sub</b>

Please somebody throw some light on my problem.

Thanks in Advance,

Regards

Sam Mathew