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