Adding a conditionnal Copy/Paste function to archive data causing issues with working VBA

Hey everyone,

I'm trying to tweak a VBA code I've been using for a couple of years, and thought it would be pretty simple...but for some reasons, I can't seem to make it work.

I'm using this procedure to retrieve stock data from a website. Basically, it goes through a table where I have a bunch of different URLs (llisted in column L). If currently maked as being active (an "X" in column K), it first makes sure to clear the previous data contained in columns R:AF and then returns 14 different values in columns R:AE. Column AF is a Timestamp I added (a basic NOW function), so it's not coming from the website. Now that part works fine.

What I'm trying to achieve is to copy values from AW:AY to AZ:BB before it clears the data, using the same criterias ("X" in column K, column L <> "").

I've tried a bunch of different things, but they pretty much ended with the same result; the .copy and .pastespecial are working, but as soon as the copy/paste is complete, my refresh counter stays stuck at 1 of #### and nothing moves.

Here's the code I'm using:

Please Login or Register  to view this content.

I tried to tweak the 'copie et efface contenu information' part to

Please Login or Register  to view this content.

...and a bunch of other things. I tried having two different FOR at the beginning, one that would first copy/paste the data, then a second one that would clear the data, didn't work. Also tried to move the 'copie et efface' part where the "For i" is (it's force a delay so that we won't get timedout by the site). Again didn't work. Did some test with Application.ScreenUpdating too...didn't solve the issue.

One thing to note, it does seem to work with a smaller size table or selection (ie. less "X" in column K). But when finished, that table should be about 7,000 rows long...and we need to be able to refresh the whole table at night. But right now, it just seems like this is too much for Excel to handle.

Any ideas ?

Sample file is attached, VBA is in Module "REFRESH" if someone is willing to take a look.
There's a button on the TEST tab and another one on the REFRESH tab (at the top, near column AA)

Thanks !

Originally Posted by JLGWhiz

The above is not what the below does. The below simply changes the content of a range to value v. formula.

Please Login or Register  to view this content.

Yeah, sorry, my mistake...second line should've been AZ and BB instead of AW and AY.

Tried your code. Unfortunately, the same issue presented itself. It copies all the values from AW:AY to AZ:BB...then the counter appears, but stays stuck at 1.

The closest I got it to work is by removing the 'copie et efface' part completely, and placing the .Copy / .PasteSpecial / .ClearContents part in the second IF, like this:

Please Login or Register  to view this content.

Not sure it this is structuraly correct (I'm going more by instinct than actual knowledge to be honest) but for some reasons, it seems to handle it better that way, by treating it line-by-line, intead of copying/clearing everything at first and then refreshing the data. But even then, I'm usually getting stuck at around 200/#### on the counter.

Originally Posted by JLGWhiz

I took a look and cannot see any reason why the copy event entered above the loop that connects to the web and refreshes would interfere with the refresh counter. Frum what I see, the counter does not rely on Columns AW:BB for its calculations and nothing in the loading and posting of the array is reliant on those six columns. So theoretically, that loop should run as epected, even with the copy action having taken place beforehand. However, my eyes and brain are in the process of decay due to age, so I might have missed something. If you have not already done so, I suggest you step through the code line by line using the F8 function key to see if you can spot any unexpected values for the variables, or other anomally that might affect the refresh statement.

Thanks for taking the time to look it up, really appreciated.

Unfortunately, I realized that I couldn't place the .Copy / .PasteSpecial / .ClearContents with the delay timer, as it will prevent me from using a 'missing' VBA if the lines aren't cleared at the beginning. So I started from scratch with a new workbook and a new 195-rows table. Added another FOR event just before the .ClearContents one and turned off ScreenUpdating. Again, not entirely sure this is all structurally correct, but right now it works...with 195 rows. I'll run some larger tests tomorrow.

Code right now:

Please Login or Register  to view this content.

I'll leave the topic open, just in case anyone else would wanna pitch in.
I'll make sure to lock it in the next 48-hours.

Thanks again for your input, really appreciated.

UPDATE
So, as I suspected, same issue presented itself as soon as I expanded the table from 195 to 1,000. Basically, the copy/paste worked fine...but as soon as it reached the refresh part, Excel became unresponsive (can't even stop the macro by pressing escape) and counter wouldn't move from 1/####. Not exactly sure what the problem was...it felt like Excel was trying to calculate something right after the copy/paste (which shouldn't happen since I'm turning calculation off at the very beginning) and that it couldn't handle both at the same time, causing Excel to crash. At least, that's what it felt like.

The good news is...after tons of different tweaks and unsuccesful attempts, I finally got it to work.

Basically, I added a third section of 3 columns to temporarily store the previous data in fixed values format (taken from formulas). But instead of copying line-by-line, I now copy the whole columns. Excel seems to handle it better that way.

Please Login or Register  to view this content.

Once that's done, I can now clear the table with the event I already had.

Please Login or Register  to view this content.

Now that the previous data has been stored and that the appropriate lines have been cleared, the counter starts and it will refresh only the lines needed.
So I added three VA(#) within that part of the procedure, right after the timestamp, to duplicate the previsouly archived data (step 1) in fixed values again. By doing so, it only does it for the lines covered by the refresh, and leave the other ones untouched.

Please Login or Register  to view this content.

...might now be the most elegant way to achieve what I wa
nted, but FINALLY, I was able to make a full refresh of a 5183 lines table !

Time to lock this up !
Thanks again !