+ Reply to Thread
Results 1 to 12 of 12

Need help modifying a macro to insert rows after date change.

  1. #1
    Registered User
    Join Date
    11-15-2013
    Location
    Newquay, Cornwall
    MS-Off Ver
    Excel 2010
    Posts
    67

    Need help modifying a macro to insert rows after date change.

    Hello all you lovely helpful people.

    I have a sheet with a list of jobs and the date which they need to be dispatched by in column 'M'.

    I have been trying to find/make a macro that will insert a blank (but coloured) line before the row which contains 'todays' date. It then needs to insert a coloured line after each date change.

    I have found this macro which does the latter. I just need to make a change so that the first line is inserted before todays date, then goes down the list and inserts a line after each date change.

    Please Login or Register  to view this content.
    This macro does however bring up a run-time error '13', Type mismatch.

    And I need the inserted rows to be coloured. Can anyone help me modify this? Many thanks in advance.

  2. #2
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: Need help modifying a macro to insert rows after date change.

    try this way:

    Please Login or Register  to view this content.
    and let me know...
    .. and don't forget to have fun!
    Bogdan.

    mark SOLVED and Add Reputation if my answer pleases you

  3. #3
    Registered User
    Join Date
    11-15-2013
    Location
    Newquay, Cornwall
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Need help modifying a macro to insert rows after date change.

    Excellent! And thanks for the quick response.

    So the code you posted does the initial 'inserting line before todays date'.

    So can I then add the code in my original post after that to go through the rest of the sheets after todays date and add a line after each date change?

    eg.....

    Please Login or Register  to view this content.
    Does the second part of the code need modifying to start after todays date? Much appreciated :-)

    edit: It doesn't like having two Dim as Long. What can I change the second one to?
    Still learning... one day I will be able to help!

  4. #4
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: Need help modifying a macro to insert rows after date change.

    Can you attach the workbook so i can see the structure of the dates? Please hide any sensible confidential data.

  5. #5
    Registered User
    Join Date
    11-15-2013
    Location
    Newquay, Cornwall
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Need help modifying a macro to insert rows after date change.

    Should hopefully be attached. I have removed some of the rows with sesitive data, so column 'M' (which did have the dates) is now column G.

    I have also added the colour lines so you can see what I would like the end result to look like.

    So there is normally between 0-10 rows before todays date. Then after that there could be any number of date groupings, as you should be able to see.

    EXAMPLE.xlsx

  6. #6
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: Need help modifying a macro to insert rows after date change.

    So

    Please Login or Register  to view this content.
    Put this in a module and run it on the worksheet you need.

  7. #7
    Registered User
    Join Date
    11-15-2013
    Location
    Newquay, Cornwall
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Need help modifying a macro to insert rows after date change.

    AWESOME! It works!

    So I just needed to combine the IF statements.

    Thank you very much for your help. Hopefully in a year or two I will know enough to help people like you do!


  8. #8
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: Need help modifying a macro to insert rows after date change.

    Glad to help. Mark SOLVED and Add Reputation if my answer pleases you.

  9. #9
    Registered User
    Join Date
    11-15-2013
    Location
    Newquay, Cornwall
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Need help modifying a macro to insert rows after date change.

    Hello again bulina2K,

    I wonder if you might be able to help make a slight modification to this. We have been a little too efficient here, and managed to get all the backlog product out of the door.

    This means, that the first part of the macro that inserts a line before "todays date" comes back as a run time error, because there is nothing before "todays" date.

    Is there a modification that can be added to the 'If' statement to ignore it if there is nothing before "todays date"?

    Thanks again.

  10. #10
    Valued Forum Contributor bulina2k's Avatar
    Join Date
    11-20-2012
    Location
    Urziceni, Ialomita, Romania
    MS-Off Ver
    2019 and 365
    Posts
    867

    Re: Need help modifying a macro to insert rows after date change.

    add this line of code first thing in Do loop

    Please Login or Register  to view this content.
    so:

    Please Login or Register  to view this content.
    let me know..

  11. #11
    Registered User
    Join Date
    11-15-2013
    Location
    Newquay, Cornwall
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Need help modifying a macro to insert rows after date change.

    Sweet, that has fixed it. Many thanks once again. Wish I knew it was that simple!

  12. #12
    Registered User
    Join Date
    11-15-2013
    Location
    Newquay, Cornwall
    MS-Off Ver
    Excel 2010
    Posts
    67

    Re: Need help modifying a macro to insert rows after date change.

    Ignore, I'll start a new thread.
    Last edited by Graham Pall; 07-22-2015 at 07:42 AM. Reason: Rules probably.

+ 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. Macro to cut two rows and insert them in different sheet in order of date
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-28-2014, 12:58 PM
  2. Make ranges in a sort macro change when I insert rows
    By Paul Sheppard in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-07-2012, 08:57 AM
  3. Insert row after date change
    By nana15nuna in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-24-2012, 11:43 PM
  4. Insert Blank rows after every date change
    By josigrozi in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-15-2011, 07:23 AM
  5. [SOLVED] macro to Insert rows at every change in a column
    By karthikr22@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2005, 09:05 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