+ Reply to Thread
Results 1 to 8 of 8

Copy and Paste based on Date VBA

  1. #1
    Registered User
    Join Date
    12-07-2018
    Location
    canada
    MS-Off Ver
    2018
    Posts
    28

    Copy and Paste based on Date VBA

    Hi there,

    I need to specify a date and if it matches a date in column A in a differet workbook i need to copy the whole row and paste it in a different workbook, and then loop through and copy/paste

    So far I have the code below but for some reason its not copying or pasting

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,635

    Re: Copy and Paste based on Date VBA

    why not just filter and copy it all at once?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    12-07-2018
    Location
    canada
    MS-Off Ver
    2018
    Posts
    28

    Re: Copy and Paste based on Date VBA

    because I have multiple workbooks that I need to scroll through and do the same thing to. Ideally it would open all the excel documents in a specific folder and do this to each one of them... that's just how I receive the data

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

    Re: Copy and Paste based on Date VBA

    Are you not getting an error?
    You are trying to "squeeze a quart into a pint pot"

    which rows should be copied?
    needs specifying
    Please Login or Register  to view this content.
    Rows = ALL rows
    Will not fit unless you paste to A1
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  5. #5
    Registered User
    Join Date
    12-07-2018
    Location
    canada
    MS-Off Ver
    2018
    Posts
    28

    Re: Copy and Paste based on Date VBA

    I switched it to

    Please Login or Register  to view this content.
    but its still not working. I think it may not be recognizing the dates for the rows i need to copy. As well I'm not sure if its cycling between the workbook correctly

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

    Re: Copy and Paste based on Date VBA

    Try this amended version of your code - which works for me

    Please Login or Register  to view this content.
    1. Let me know how you get on

    Ideally it would open all the excel documents in a specific folder and do this to each one of them..
    2. Is it consistently the first sheet in every workbook, with headers in row 2 and data staring in row 3 ?

    why not just filter and copy it all at once?
    3. This question from @humdingaling deserves further consideration. Filtering the data is MUCH faster than looping through rows
    Last edited by kev_; 01-30-2019 at 03:08 AM.

  7. #7
    Registered User
    Join Date
    12-07-2018
    Location
    canada
    MS-Off Ver
    2018
    Posts
    28

    Re: Copy and Paste based on Date VBA

    Thanks so much, it works perfectly and I was able to find to code any modify it was well through scroll through all worksheets in a folder

  8. #8
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,635

    Re: Copy and Paste based on Date VBA

    i did mean filter and copy via CODE not manually....no idea how big your sheet is or how many "hits" per macro run you are going to get
    just doesnt seem like it will scale very well ....

    if you had 50000 rows it would have to loop 50000 times then if you had 10000 hits then you would copy and paste 10000 times
    vs
    filter once, copy filtered results paste in one go. No looping required

    anyways it seems like you are happy with your solution

+ 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. copy paste based on a date
    By namluke in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-11-2015, 10:39 AM
  2. [SOLVED] Copy Paste Based on Date
    By RedMug in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-25-2014, 01:50 PM
  3. Vba to copy and paste based on date criteria
    By thedeadzeds in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-16-2014, 11:08 AM
  4. Copy/Paste based on Date Range
    By smmeader in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-13-2012, 09:24 AM
  5. Copy Paste row from one worksheet to another based on DATE
    By niketmohan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-21-2011, 08:29 AM
  6. Copy row based on date and then paste into a new worksheet based on section number
    By calmlaunch in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-25-2011, 11:40 PM
  7. Copy-Paste based on date in cell
    By yunesm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-28-2009, 04:18 PM

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