+ Reply to Thread
Results 1 to 11 of 11

Copying multiple rows if a certain cell in those row meets a criteria

  1. #1
    Registered User
    Join Date
    11-07-2018
    Location
    England
    MS-Off Ver
    365
    Posts
    6

    Copying multiple rows if a certain cell in those row meets a criteria

    I am trying to develop a macro which easily lets me transfer energy bill data from on workbook to a table in another. I've made the part which simply copys and pastes rows, but now i need to find a way for it to work out which rows need to be copied for itself. This is a problem because my criteria is a bit awkward

    i only want rows to be copied if there aren't any duplicates in the destination table (most of the data in the source file will already be in the destination table, so i obviously dont want any duplciates). To avoid copying duplicates, i need to make sure both the date and building aren't found together in a row in the destination table, but i really dont know how im going to write such a calculation in VBA.

    Here is the code i currently have, it only copies and pastes a single row into the destination table.

    Please Login or Register  to view this content.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Copying multiple rows if a certain cell in those row meets a criteria

    It would be easier to help and test possible solutions if you could attach a copy of your file. Give a detailed explanation of what you want to do referring to specific cells, rows, columns and worksheets.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    11-07-2018
    Location
    England
    MS-Off Ver
    365
    Posts
    6

    Re: Copying multiple rows if a certain cell in those row meets a criteria

    It wont let me upload an attachment for some reason, so a screenshot will have to do. just imagine each table is on its own workbook.
    Capture.PNG

    Ok so ill try to explain this again:
    The rows in the source table highlighted in red are alrteady present in the destination table, so we DONT want to copy them over to the destination table.
    So i want to work out which rows need to be tranfered by matching the centre, date, energy usage and net total to a single row in the destination table,
    every single cell must match for it to be classified as a duplicate.

    I know how to paste columns from one table to another, but not across workbooks, and i dont know how to copy only the rows which match my criteria.

    Thank you
    Attached Images Attached Images

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Copying multiple rows if a certain cell in those row meets a criteria

    Place this macro in a standard module in your Source workbook. Change the source sheet name (in red), the destination sheet name (in blue) and the destination workbook name (in orange) to match your actual names. Make sure that both workbooks are open before you run the macro.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-07-2018
    Location
    England
    MS-Off Ver
    365
    Posts
    6

    Re: Copying multiple rows if a certain cell in those row meets a criteria

    Thank you so much, this worked as expected. However, after implementing it i've realised there are some tweaks that i need to make which i didn't consider when first describing my problem.

    Firstly, there are some columns in the source table which i DON't want copied over to the destination table, but there are columns after it which i do (see the column labelled "day rate").

    Second, I need to insert text in a column in the destination table which doesn't exist in the source table. (see the column labelled "fuel", i need every row which gets copied over to have "electricity" written in that column automatically)

    Thanks again for your help

    Capture.PNG

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Copying multiple rows if a certain cell in those row meets a criteria

    Try:
    Please Login or Register  to view this content.

  7. #7
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Copying multiple rows if a certain cell in those row meets a criteria


  8. #8
    Registered User
    Join Date
    11-07-2018
    Location
    England
    MS-Off Ver
    365
    Posts
    6

    Re: Copying multiple rows if a certain cell in those row meets a criteria

    This works great, thank you so much! last thing, is there any way i can remove the source formatting of the copied rows when they paste onto the new table?

    Also, thanks for listing the cross post for me. I included a link on the other post but forgot to put one here.

    Thanks again for your help.

  9. #9
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Copying multiple rows if a certain cell in those row meets a criteria

    Try:
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    11-07-2018
    Location
    England
    MS-Off Ver
    365
    Posts
    6

    Re: Copying multiple rows if a certain cell in those row meets a criteria

    Perfect! You've been a huge help, thank you.

  11. #11
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,775

    Re: Copying multiple rows if a certain cell in those row meets a criteria

    My pleasure.

+ 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] Copying entire rows of data if a cell meets certain criteria
    By dmchase in forum Excel General
    Replies: 7
    Last Post: 04-06-2016, 09:24 AM
  2. Replies: 3
    Last Post: 10-30-2013, 02:05 PM
  3. Delete rows if cell value meets certain criteria
    By jlax34 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-30-2013, 03:57 PM
  4. Copying Cell That Meets Certain Criteria
    By PapaCheese in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-20-2012, 08:11 PM
  5. Copying Cell That Meets Certain Criteria
    By PapaCheese in forum Excel General
    Replies: 0
    Last Post: 02-20-2012, 05:40 PM
  6. Replies: 3
    Last Post: 01-24-2011, 08:35 PM
  7. List Rows where a cell meets criteria
    By kmurray24 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-26-2008, 07:29 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