+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Forum Contributor
    Join Date
    08-28-2015
    Location
    Montreal, Canada
    MS-Off Ver
    2013 on PC, 2011 on MAC
    Posts
    158

    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 !
    Attached Files Attached Files

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

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

    What I'm trying to achieve is to copy values from AW:AY to AZ:BB
    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.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

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

    Try this and see if you still have the problem.

    Please Login or Register  to view this content.
    Last edited by JLGWhiz; 09-09-2020 at 12:05 PM.

  4. #4
    Forum Contributor
    Join Date
    08-28-2015
    Location
    Montreal, Canada
    MS-Off Ver
    2013 on PC, 2011 on MAC
    Posts
    158

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

    Quote Originally Posted by JLGWhiz View Post
    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.

  5. #5
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

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

    Well, I confess that I did not take a close look. Just jumped on the copy issue. I didn't look at the counter, but I will. These are usually a matter of logical seequence ans you have alluded to by placing the statement in a different location. If I can come up with something better I will post back to this thread.

  6. #6
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

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

    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.

  7. #7
    Forum Contributor
    Join Date
    08-28-2015
    Location
    Montreal, Canada
    MS-Off Ver
    2013 on PC, 2011 on MAC
    Posts
    158

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

    Quote Originally Posted by JLGWhiz View Post
    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.

  8. #8
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

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

    Thanks for the feedback,
    regards, JLG

  9. #9
    Forum Contributor
    Join Date
    08-28-2015
    Location
    Montreal, Canada
    MS-Off Ver
    2013 on PC, 2011 on MAC
    Posts
    158

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

    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 !

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy and paste function not working
    By krisryan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-14-2018, 11:16 AM
  2. Macro to Archive (cut and paste) data from one worksheet to another
    By ExcellentAmanda in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-08-2018, 09:34 PM
  3. Excel to Outlook Calendar date duplication issues and blank cells causing issues
    By singerbatfink in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 02-11-2016, 08:07 AM
  4. [SOLVED] Excel to Outlook Calendar date duplication issues and blank cells causing issues
    By singerbatfink in forum Outlook Formatting & Functions
    Replies: 0
    Last Post: 02-11-2016, 07:57 AM
  5. Copy/Paste Function not working properly in Loop
    By scheckkr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-17-2015, 01:33 PM
  6. Copy Paste Code not working after adding another area
    By Aeneren in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-05-2012, 02:39 AM
  7. Copy and PASTE function not working
    By quikgun15 in forum Outlook Formatting & Functions
    Replies: 1
    Last Post: 08-26-2011, 05:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1