+ Reply to Thread
Results 1 to 5 of 5

Macro to change cell for each value in range, copy and paste dynamic results

  1. #1
    Registered User
    Join Date
    09-22-2015
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    5

    Macro to change cell for each value in range, copy and paste dynamic results

    Thank you all in advance, I have a worksheet that includes a dynamic web query. There are 3 inputs: a year, a location ID, and a statistic ID that are all part of their own data validation range. Currently, every time I change one of those inputs the web query updates successfully. What I'd like to do is run a macro that updates the 3 inputs to all possible values and after each change copy and paste the new web query results to a separate sheet.

    The year input cell is E2, from data validation range A2:A12
    The location ID cell is C9, from data validation range B14:B63
    The statistic ID cell is C8, from data validation range B65:B633

    The range to copy and paste to a new sheet is G:P and can be pasted in new sheet at A1.

    Thank you!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,963

    Re: Macro to change cell for each value in range, copy and paste dynamic results

    That's a hell of a lot of combinations and I suspect it could be perceived as a DoS attack on the web site.

    https://en.m.wikipedia.org/wiki/Deni...service_attack


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    09-22-2015
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    5

    Re: Macro to change cell for each value in range, copy and paste dynamic results

    I appreciate the heads up. I guess I could look to do them in much smaller increments, considering one of the inputs is year. I could keep the year static and do it over a longer period of time. Any ideas on the code, even if the request is smaller? Additionally, this is one of the early lines in that copy and pasted Wikipedia reference: A distributed denial-of-service (DDoS) is where the attack source is more than one–and often thousands–of unique IP addresses. It would be from one IP address, and wouldn't be any different than if I did each request one by one, which I have access to.
    Last edited by DFSGolfMetrics; 09-22-2015 at 08:20 PM.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,963

    Re: Macro to change cell for each value in range, copy and paste dynamic results

    Well, it's just a triple nested loop: for i=2 to 12, for j=14 to 63, for k=65 to 633. But my issue/concern would be the 50 locations by 570+ statistics; that's 28,500+ iterations. If you did include the year, that would be 313,500+ iterations. In the inner loop, you move the cell values to the query cells and fire the update.

    I suspect timing might be an issue, waiting for the query to update before copying and pasting the results.

    In theory, it's doable, but it would be somewhat resource hungry and probably slow

  5. #5
    Registered User
    Join Date
    09-22-2015
    Location
    New York, NY
    MS-Off Ver
    2013
    Posts
    5
    Yeah we realize we may only need to use it for 20-30 statistics, just the original thought of having all of them was enticing. How/where would you put in the code for copying to another workbook/sheet?

+ 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. Macro to copy and paste data from dynamic cell once per day
    By lintorre in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-31-2014, 06:30 PM
  2. Replies: 8
    Last Post: 12-06-2013, 01:42 PM
  3. [SOLVED] Macro to copy/paste cell into same column down a dynamic number of rows
    By rdowney79 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-31-2013, 03:03 PM
  4. [SOLVED] Macro to copy/paste dynamic range x times into same sheet
    By BigAl_Qld in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-19-2013, 03:48 AM
  5. paste RNG numbers
    By kaohik78 in forum Excel General
    Replies: 0
    Last Post: 09-26-2012, 10:30 PM
  6. Change range of cells within VBA macro and copy and paste to fixed cell range
    By Mannyny in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-20-2012, 11:51 AM
  7. VBA macro to Find a dynamic range of cells, copy then paste to another sheet
    By Bmxerdude2087 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-24-2012, 02:36 PM

Tags for this Thread

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