Skip to Content
0
Former Member
Nov 21, 2006 at 06:28 PM

how to run a VB macros code on Query refresh?

348 Views

Hi BW gurus,

Please help me out with this task.

I have a macros as below

<b>Sub Table_To_Cons()

'Copy the occupied rows

Application.DisplayAlerts = False

TargetSheet = ActiveSheet.Name

Sheets("Table").Activate

j = 2

For i = 21 To 65536

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

'MsgBox "The Cell is Empty"

Else

f = Range("F" & i).Value

g = Range("G" & i).Value

Sheets("Consolidated Data").Range("A" & j).Value = f

Sheets("Consolidated Data").Range("B" & j).Value = g

j = j + 1

End If

Next

Sheets("Consolidated Data").Activate

MsgBox "Finished"

' Update Chart

Sheets("Chart1").Select

ActiveChart.PlotArea.Select

ActiveChart.SetSourceData Source:=Sheets("Consolidated Data").Range("A2:B6") _

, PlotBy:=xlRows

End Sub</b>

User are not interested to run the VB macros manually.

Instead they are asking like this.The macros should run and show the latest result everytime when we refresh the query.

Can someone help me out how to achieve this.

Any kind of help is appreciated.

Thanks in Advance

Have a nice day

Regards

Sam Mathew