+ Reply to Thread
Results 1 to 7 of 7

Nested Loop does not transfer value of each cell in Range to another worksheet!

  1. #1
    Registered User
    Join Date
    04-24-2019
    Location
    Bogota,Colombia
    MS-Off Ver
    2013
    Posts
    42

    Nested Loop does not transfer value of each cell in Range to another worksheet!

    Hi everyone,

    I wrote a nested loop to transfer the values of each cell in a named range to another worksheet in the same workbook. However, it transfers only the value of the first cell in the range to the assigned cell in the recipient sheet. I have tried anything I could thing of such as defining the range as object instead of range but nothing works. Any help would be much appreciated!

    The code should transfer the data from the named range "FPlannerM1" to the first available row in "ToSheet" in only one row, example A2 B2 C2 D2 etc, when the info in the range is changed, then the macro should do the same on; A3 B3 D3 and so on.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by realbestpete; 04-29-2019 at 11:28 PM. Reason: I attached a file

  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
    44,086

    Re: Nested Loop does not transfer value of each cell in Range to another worksheet!

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    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
    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
    44,086

    Re: Nested Loop does not transfer value of each cell in Range to another worksheet!

    In the absence of a sample workbook and an idea what the code should produce, maybe this will work for you:

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    04-24-2019
    Location
    Bogota,Colombia
    MS-Off Ver
    2013
    Posts
    42

    Re: Nested Loop does not transfer value of each cell in Range to another worksheet!

    Thanks for your reply TMS,

    Your code works fine except it transfers the data in the range "FPlannerM1" in 3 columns as the original set and I require it to be transferred across to "ToSheet" in the first available row, for example; A2 B2 C2 D2 etc and when I update the range and run the macro, on A3 B3 C3 D3 and so on, I have attached a workbook showing this. Thanks
    Attached Files Attached Files

  5. #5
    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
    44,086

    Re: Nested Loop does not transfer value of each cell in Range to another worksheet!

    Your code works fine except it transfers the data in the range "FPlannerM1" in 3 columns as the original set
    That would be because I had to guess at the shape of the Named Range and also where it was intended to go. Having the sample workbook makes a big difference in achieving a working solution that meets your needs.

    Anyway, whatever, try this:

    Please Login or Register  to view this content.

    There are other ways that might be quicker with very large amounts of data. For example, you could read the whole of the Named Range into an array and then loop through the array elements creating a second array. You can then drop that straight into the output range.

  6. #6
    Registered User
    Join Date
    04-24-2019
    Location
    Bogota,Colombia
    MS-Off Ver
    2013
    Posts
    42

    Re: Nested Loop does not transfer value of each cell in Range to another worksheet!

    Thanks a lot TMS, it does just what I required! I appreciate your help!!!

  7. #7
    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
    44,086

    Re: Nested Loop does not transfer value of each cell in Range to another worksheet!

    You're welcome. Thanks for the rep.

+ 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. Replies: 3
    Last Post: 01-09-2017, 05:22 PM
  2. Nested For Each loop for 2 range variables
    By artur.serra in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-03-2013, 10:34 AM
  3. [SOLVED] nested loop for copying range: error no cells were found
    By Edejager in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-09-2013, 08:52 AM
  4. [SOLVED] Can't get nested Loop to copy range values from 2 tables working correctly
    By gtol in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-05-2013, 12:01 PM
  5. [SOLVED] Problems with nested loop to copy a range
    By Edejager in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-04-2013, 10:40 AM
  6. Next without For error in nested loop - Escaping a Nested Loop?
    By BeneRich in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-10-2012, 06:38 PM
  7. For each loop nested; getting stuck on one cell in first iteration of nested loop
    By Excel_vba in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-22-2009, 11:54 AM

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