Help with Not Responding error while runnign a macro

[sudarshan1026]

Hi All,

I am using the below macro to copy contents from sheet 1 where a web query refreshes into sheet 2.

The sheet gets hung and freezes while running this code. The code works well when i run it using F8 in the editor.

Please Login or Register  to view this content.

Any help on this would be appreciated.

Thanks
Sudi

[Philb1]

I gets stuck in an infinite loop if E4 is empty
It copies once, refreshes the workbook ? Why? & then gets stuck in While/Wend
What are you trying to do?

[sudarshan1026]

Hi,

Let me explain more clearly.

I have a web query running and it refreshes the tables. The reason why i have used E4 cell is to check if the query has refreshed or not. When the query refreshes it comes out of the loop and copies the values in G2:N2 which is consolidated result of the table using simple countif formula.

The values in Col A are the values for which i need the relevant values from the table.

The code worls fine when i run it using F8 barring it copies the values twice into sheet 2.

Hope i was clear in explaining the scenario.

Thanks in advance.

Sudi

[Philb1]

Hi Sudarshan
Have you thought about a worksheet change macro? It means you won't get stuck in an infinite loop. Here's the code
If it's of any interest to you, you could change it here & there to suit your needs
I've attached a working sample for you to play with

In Sheet(1)

Please Login or Register  to view this content.

In ThisWorkbook sheet. It enters 1 in sheet 1 D4 when workbook opens & deletes the cell when the workbook closes.
The number is used to update the lng value in the change event macro. Change address if you like

Please Login or Register  to view this content.

In a standard module just for demonstration I put

Please Login or Register  to view this content.

Delete it if not required. Its only for demo purposes

If anyone can improve on it, I'd be happy to see it. Always keen to learn
Cheers
Phil