+ Reply to Thread
Results 1 to 9 of 9

VBA code to copy/paste rows conditionally based on cell value in column

  1. #1
    Registered User
    Join Date
    04-14-2012
    Location
    Paris, France
    MS-Off Ver
    Excel 2010
    Posts
    25

    VBA code to copy/paste rows conditionally based on cell value in column

    Hello,

    I would like some help figuring out how to do the following:

    In one sheet I have 5 columns: Location, Item, Description, Qty, Unit Price.

    Sheet 1.png

    The objective is to comb through the Location column and copy the Location value in a new sheet and then copy all the rows that have that same Location value and past them underneath.

    Sheet 2.png

    Any help will be appreciated! Thanks!

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: VBA code to copy/paste rows conditionally based on cell value in column

    a pivot table gets you pretty close in one step without any cutting, pasting or sorting

    see attached workbook
    add or change details in sheet "Data" then refresh the pivot table

    PivotRefresh.jpg
    Attached Files Attached Files
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA code to copy/paste rows conditionally based on cell value in column

    Does this help?

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    04-14-2012
    Location
    Paris, France
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: VBA code to copy/paste rows conditionally based on cell value in column

    Hey Kev, thanks for the input however a pivot table won't do the job 100% since I'm intending on using the code in a template.

  5. #5
    Registered User
    Join Date
    04-14-2012
    Location
    Paris, France
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: VBA code to copy/paste rows conditionally based on cell value in column

    Thanks John. This is perfect.

  6. #6
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: VBA code to copy/paste rows conditionally based on cell value in column

    @John H.Davis
    Perhaps I'm missing something obvious ......
    - what is purpose of array y?
    - I don't spot any values being deposited in the array

  7. #7
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA code to copy/paste rows conditionally based on cell value in column

    @ thedefense - You're welcome. Glad to help out and thanks for the feedback and the reps.

    @ kev_ - The purpose of array y is to add speed to the Loop for the variable i.

  8. #8
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: VBA code to copy/paste rows conditionally based on cell value in column

    @John H.Davis

    so this
    Please Login or Register  to view this content.
    runs faster than

    this
    Please Login or Register  to view this content.
    Interesting for large data sets - why is it faster?

    apologies for being a pain!
    thanks

  9. #9
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: VBA code to copy/paste rows conditionally based on cell value in column

    @ kev - Sorry for the late reply, been busy. Yes looping large data sets using an array tends to run faster then using a normal loop.
    Though there many other resources too support this here is one link.

    https://stackoverflow.com/questions/...s-in-vba-excel. Hope that helps.

+ 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: 1
    Last Post: 01-04-2017, 06:00 PM
  2. Replies: 1
    Last Post: 01-04-2017, 05:59 PM
  3. VBA code to copy n rows of data and paste into every second column
    By frankbrett in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-19-2016, 03:30 PM
  4. [SOLVED] Copy rows/range and paste n number of times based on last used cell in Column A
    By skonduru in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-29-2016, 02:30 PM
  5. Need code to copy and paste based on variable rows
    By bonnie6850 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-04-2013, 01:51 AM
  6. [SOLVED] VB Code to Copy & Paste based on Excel Column Value?
    By MissMeliss in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-10-2012, 05:02 PM
  7. copy rows on cell based value and condtionaly paste it to other column
    By sanketgroup in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-30-2011, 09:43 AM

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