+ Reply to Thread
Results 1 to 6 of 6

VBA formula to copy format and paste cells for dates?

  1. #1
    Registered User
    Join Date
    07-12-2015
    Location
    London, England
    MS-Off Ver
    2011
    Posts
    8

    VBA formula to copy format and paste cells for dates?

    I've got this code


    Please Login or Register  to view this content.
    Let's say I have something like this..

    Assignment 1 start Assignment 1 end Assignment 2 Start Assignment 2 end
    1/10/2007 2/12/2008 5/6/2005 1/9/2006
    1/20/2008 2/20/2009 2/21/2009 2/27/2009


    What it should do is take the two dates that are before the first two, (5/6/2005 and 1/9/2006 and replace them with 1/10/2007 and 2/12/2008 and then put 5/6/2005 and 1/9/2006 into the first two cells of the row.)
    The second row is fine because they're all chronologically placed.

    Instead I get something like this

    Assignment 1 start Assignment 1 end Assignment 2 Start Assignment 2 end
    5/6/2005 1/9/2006 5/6/2005 1/9/2006
    1/20/2008 2/20/2009 2/21/2009 2/27/2009

    where it just copied the last two and didn't paste the first two inplace of the last two. Any ideas? I'd like to preserve formatting of the cells as well because the colors I have on the cells help me differentiate assignments from different contractors. So assignment 1 start and end might be green, and assignment 2 start and end might be blue. I'd like to keep them those colors when switched.

    Thanks in advance.

  2. #2
    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
    43,720

    Re: VBA formula to copy format and paste cells for dates?

    If the dates are meant to be in chronological order, why don't you just loop through and sort each row (no headers, left to right)?


    Regards, TMS
    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


  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: VBA formula to copy format and paste cells for dates?

    Try this...

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 07-20-2015 at 06:53 PM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Registered User
    Join Date
    07-12-2015
    Location
    London, England
    MS-Off Ver
    2011
    Posts
    8

    Re: VBA formula to copy format and paste cells for dates?

    That's awesome! Thanks so much!!! My only question is I see that you must have copied this part of my original code

    Please Login or Register  to view this content.
    Is there any way to get rid of that bit in the code you just gave me? Other than that it's perfect!

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: VBA formula to copy format and paste cells for dates?

    Quote Originally Posted by Panglossian View Post
    That's awesome! Thanks so much!!! My only question is I see that you must have copied this part of my original code

    Please Login or Register  to view this content.
    Is there any way to get rid of that bit in the code you just gave me? Other than that it's perfect!
    I don't follow what that means.

  6. #6
    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
    43,720

    Re: VBA formula to copy format and paste cells for dates?

    Using Sort, as I originally suggested:

    Please Login or Register  to view this content.

    Regards, TMS

+ 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 to other sheets depending on dates specified in cells
    By ufopilot in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-02-2015, 05:43 AM
  2. copy abbreviated format add a , add a cells value and paste to only blank cells
    By kleptilian in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-23-2014, 02:26 PM
  3. Copy and Paste dates then chnage the format to show the month
    By spiritbarbie in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-10-2014, 12:18 PM
  4. [SOLVED] Macro to copy and paste cells on multiple sheets based on dates
    By canyflyer in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 08-21-2013, 06:24 PM
  5. Copy Format and paste to range of cells
    By Longpod in forum Excel General
    Replies: 4
    Last Post: 06-27-2012, 02:22 AM
  6. copy and paste cells with roll over dates
    By pani_hcu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-20-2011, 01:43 PM
  7. Replies: 1
    Last Post: 10-21-2010, 07:01 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