+ Reply to Thread
Results 1 to 11 of 11

How to copy and paste data into rows based on criteria

  1. 02-24-2020, 11:23 AM #1
    blucas024
    blucas024 is offline
    Registered User
    Join Date
    02-24-2020
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2016
    Posts
    4

    How to copy and paste data into rows based on criteria

    I am attempting to find a way write a VBA to copy a range of cells and paste them into a different column if it meets the criteria. I have attached my sample data. I want to copy cells D7:D129 and paste them into the same cells of any columns that are for previous weeks. I have a cell that displays the current week (B4) and cells above each column that have the week they represent (D2:T2). So in the attached example, it is week 4 and so I would want to have the cells pasted into columns E and F (weeks 2 and 3).

    Thanks in advance!
    Attached Files Attached Files
    Last edited by blucas024; 02-24-2020 at 12:44 PM.
    Reply With QuoteRegister To Reply
  2. 02-24-2020, 11:59 AM #2
    maras_mak
    maras_mak is offline
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: How to copy and paste data into rows based on criteria

    I don't know if I understood you well. Test the code. 
    Please Login or Register  to view this content.
    Best Regards,
    Maras.
    Reply With QuoteRegister To Reply
  3. 02-24-2020, 12:00 PM #3
    KOKOSEK
    KOKOSEK is offline
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,742

    Re: How to copy and paste data into rows based on criteria

    I am not sure that I understand correctly but try:

    Please Login or Register  to view this content.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.
    Reply With QuoteRegister To Reply
  4. 02-24-2020, 12:12 PM #4
    blucas024
    blucas024 is offline
    Registered User
    Join Date
    02-24-2020
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2016
    Posts
    4

    Re: How to copy and paste data into rows based on criteria

    Apologizes that I didn't provide enough detail in my original post. The code copied the cells in column D and pasted it into column E as values only.

    I'm trying to copy the formulas and paste them over. Each week we get actual performance that replaces the plan that is currently in the cells. There are a dozen files like this, and rather than having to copy all the formulas and moving them over to the new column each week, I was hoping to find a way to automate this.

    Ideally I would find a way to paste the formulas into the column that represents the previous week only, but if it needs to paste into all weeks in the past, that works too.
    Reply With QuoteRegister To Reply
  5. 02-24-2020, 12:18 PM #5
    maras_mak
    maras_mak is offline
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    528

    Re: How to copy and paste data into rows based on criteria

    Try after correction. 
    Please Login or Register  to view this content.
    Last edited by maras_mak; 02-24-2020 at 12:20 PM.
    Reply With QuoteRegister To Reply
  6. 02-24-2020, 12:21 PM #6
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: How to copy and paste data into rows based on criteria

    Some comments
    It seems it is not B4 which is used but B2: It will be nice to keep B4 ( not the row as week nb)
    When pasting value in column F there is error or values are 0 , is it correct ?
    - Battle without fear gives no glory - Just try
    Reply With QuoteRegister To Reply
  7. 02-24-2020, 12:24 PM #7
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: How to copy and paste data into rows based on criteria

    Perhaps next code
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 02-24-2020, 12:30 PM #8
    blucas024
    blucas024 is offline
    Registered User
    Join Date
    02-24-2020
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2016
    Posts
    4

    Re: How to copy and paste data into rows based on criteria

    maras_mak thank you!

    It is now pasting just in the previous week. Any thoughts on how to get it to paste the formulas? It is still only pasting values in the cells.

    PCI
    Yes you are correct, it should have been B2 instead of B4 for the week reference. I was looking at the 4 in the reference cell and mixed it up. The code provided gives a runtime error.
    Reply With QuoteRegister To Reply
  9. 02-24-2020, 12:40 PM #9
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: How to copy and paste data into rows based on criteria

    New code with B2 as reference
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 02-24-2020, 12:42 PM #10
    blucas024
    blucas024 is offline
    Registered User
    Join Date
    02-24-2020
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    2016
    Posts
    4

    Re: How to copy and paste data into rows based on criteria

    PCI that one worked perfectly. Thank you very much!
    Reply With QuoteRegister To Reply
  11. 02-24-2020, 12:44 PM #11
    PCI
    PCI is offline
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Smile Re: How to copy and paste data into rows based on criteria

    Good news.
    You are welcome and thank you for the rep.
    Enjoy Excel

    P.S.
    Keep in mind you can adjust Const value for your next mofification ....!
    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. How to Copy and paste different rows into another area based on certain criteria
    By atomium in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 09-24-2019, 04:43 AM
  2. Copy paste rows based on criteria
    By jeff p in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-08-2014, 11:00 PM
  3. [SOLVED] Copy/Paste rows based on certain criteria (drop down or colors)
    By jholiday78 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-26-2014, 08:15 PM
  4. [SOLVED] copy and paste rows based on criteria in column
    By CharlieZangel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2013, 12:54 PM
  5. Copy and Paste Rows based on Criteria onto another sheet and sort based on oldest item
    By Kushal8684 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-25-2013, 05:37 AM
  6. [SOLVED] Macro to copy and paste rows to a new sheet based on criteria
    By Belo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-31-2010, 02:25 AM
  7. Copy\Paste multiple criteria based rows to new sheet
    By namz in forum Excel Programming / VBA / Macros
    Replies: 46
    Last Post: 06-06-2008, 08:08 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1