+ Reply to Thread
Results 1 to 10 of 10

How to use a For Loop to copy and paste to another sheet based on condition?

  1. 05-13-2018, 04:53 AM #1
    maym
    maym is offline
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    How to use a For Loop to copy and paste to another sheet based on condition?

    Hi all

    I need to write a For Loop to copy certain values from one sheet to another.

    In sheet 1, I need to look down col A, and if there is a value of "1", copy A:C to sheet 2 on the next available row. I also need to copy each header (only if there is a value of 1 in each section below) and insert a row between each header.

    I have outlined what I am trying to do in the attached?

    I have been trying for quite some time but I cannot seem to get it working.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-13-2018, 05:13 AM #2
    maym
    maym is offline
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: How to use a For Loop to copy and paste to another sheet based on condition?

    So far I have been able to use the following:

    Please Login or Register  to view this content.
    However it copies line for line and does not adjust for blank rows on sheet 2, and also copies the last header which is not required. Can anyone help me with this please?
    Reply With QuoteRegister To Reply
  3. 05-13-2018, 05:17 AM #3
    TMS
    TMS is offline
    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,456

    Re: How to use a For Loop to copy and paste to another sheet based on condition?

    You need a second row counter for the Target Range. You only increment the second counter when you have a valid row to copy.
    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

    Reply With QuoteRegister To Reply
  4. 05-13-2018, 05:18 AM #4
    maym
    maym is offline
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: How to use a For Loop to copy and paste to another sheet based on condition?

    thanks TMS. How can I adjust for that? I am sorry but I don't really know how to amend it.
    Reply With QuoteRegister To Reply
  5. 05-13-2018, 07:58 AM #5
    nilem
    nilem is offline
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: How to use a For Loop to copy and paste to another sheet based on condition?

    Hi maym,
    try so
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 05-13-2018, 09:28 AM #6
    TMS
    TMS is offline
    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,456

    Re: How to use a For Loop to copy and paste to another sheet based on condition?

    Nilem's solution is probably better but ...

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 05-13-2018, 07:01 PM #7
    maym
    maym is offline
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: How to use a For Loop to copy and paste to another sheet based on condition?

    many thanks to you both!! I have gone with nilem's, but what if I wanted to copy to row 20 on sheet 2? I changed this part:

    Please Login or Register  to view this content.
    to:

    Please Login or Register  to view this content.
    but it doesn't copy all of the values over?
    Reply With QuoteRegister To Reply
  8. 05-13-2018, 07:24 PM #8
    maym
    maym is offline
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: How to use a For Loop to copy and paste to another sheet based on condition?

    I used this:

    Please Login or Register  to view this content.
    However, I want to copy to col C, row 20 and the last row in col C above with data is a merged cell, so it won't allow me to copy. How can I overcome this?

    I also have data below, starting at row 100, so I can't use rows.count.endxl(up).
    Last edited by maym; 05-13-2018 at 07:55 PM.
    Reply With QuoteRegister To Reply
  9. 05-15-2018, 10:09 PM #9
    maym
    maym is offline
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: How to use a For Loop to copy and paste to another sheet based on condition?

    I ended up using nilem's solution in post #5.

    But how can I adjust so that it copies cells IF the quantity is greater than 1? At the moment, when I enter a quantity over 1, it does not seem to copy.
    Reply With QuoteRegister To Reply
  10. 05-15-2018, 10:31 PM #10
    maym
    maym is offline
    Forum Contributor
    Join Date
    10-12-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016 in 2024
    Posts
    857

    Re: How to use a For Loop to copy and paste to another sheet based on condition?

    Got it.

    Changed this:

    Please Login or Register  to view this content.
    to this:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. loop through & copy based on condition
    By cds1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-14-2018, 07:19 AM
  2. [SOLVED] Copy and paste (values) to another sheet based on condition
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-09-2013, 10:25 PM
  3. Copy& paste the data one sheet to another sheet if the condition is true
    By muvelayudham in forum Excel General
    Replies: 1
    Last Post: 09-17-2013, 01:38 PM
  4. Copy/Paste based on encountered condition within a loop
    By msmick in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-05-2011, 06:37 PM
  5. Copy paste based on a condition
    By jay36429 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-07-2011, 01:36 PM
  6. Copy and Paste Rows For certain condition then loop
    By steve70070 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-14-2011, 06:05 PM
  7. Copy/Paste from one sheet to another based on condition
    By TheTempest in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-16-2010, 01:13 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1