+ Reply to Thread
Results 1 to 12 of 12

Filling down a table to current month/date with VBA up to current month

  1. #1
    Registered User
    Join Date
    05-30-2013
    Location
    England
    MS-Off Ver
    Excel Mac V16.68 and Excel 2019
    Posts
    33

    Filling down a table to current month/date with VBA up to current month

    Hi Everyone,
    Brain struggling with this one and not sure if it's possible.

    I want to detect the current month/year and just extend the table in spanning columns A-G downwards to this month.
    Cells in column B, C, D, E, F & G have a formula that just copies down automatically then.

    Screenshot 2019-02-11 at 19.20.31.png

    I found this code online and adjusted it slightly but it goes beyond this month, into 2021 and I only want it to go to this month (not sure if it's a serial thing or?) (That's if I enter 1 as the month and the year as 2019 in the popup entry box, that is).

    Please Login or Register  to view this content.
    Is it possible to do this?

    Any help greatly appreciated, brain throbbing.

    All the best,
    Paul

  2. #2
    Forum Contributor
    Join Date
    11-29-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    104

    Re: Filling down a table to current month/date with VBA up to current month

    Does this have to be done with VBA?
    Could this be done with conditional formatting?

  3. #3
    Registered User
    Join Date
    05-30-2013
    Location
    England
    MS-Off Ver
    Excel Mac V16.68 and Excel 2019
    Posts
    33

    Re: Filling down a table to current month/date with VBA up to current month

    Hi jrean042,
    Ah, do you mean with a formula that only fills in the cells if they have a value across in columns B-G?

    If yes, I guess that could be. One question then is will the graph that is drawing from that data not just show a drop off the end, or will it detect to not draw the empty cells?

    Regards,
    Paul

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

    Re: Filling down a table to current month/date with VBA up to current month

    Try:
    Please Login or Register  to view this content.
    Last edited by Mumps1; 02-11-2019 at 04:07 PM.
    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.

  5. #5
    Registered User
    Join Date
    05-30-2013
    Location
    England
    MS-Off Ver
    Excel Mac V16.68 and Excel 2019
    Posts
    33

    Re: Filling down a table to current month/date with VBA up to current month

    Hi Mumps,
    That definitely extended the table, but beyond the date like this:

    Screenshot 2019-02-11 at 20.17.48.png

    Regards,
    Paul

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

    Re: Filling down a table to current month/date with VBA up to current month

    It's hard to work with a picture. Can you attach a copy of your file? You want to add 3 rows then, one for Dec., one for Jan. and one for Feb. Is that correct?

  7. #7
    Registered User
    Join Date
    05-30-2013
    Location
    England
    MS-Off Ver
    Excel Mac V16.68 and Excel 2019
    Posts
    33

    Re: Filling down a table to current month/date with VBA up to current month

    Hi Mumps,
    I don't seem to have a link in the toolbar at the top (maybe greyed out?) so I've popped it on Google drive here (sorry about the dull topic of the spreadsheet ):

    https://drive.google.com/open?id=1gF...e-G17nwj1ajBhH

    (I'm going to do it for each country code tab which I have code to cycle through the tabs, but for now just trying to get it working on the FI tab as an example).

    Regards,
    Paul

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

    Re: Filling down a table to current month/date with VBA up to current month

    This macro will loop through all the country sheets. Give it some time to run. You have many formulas and conditional formatting which really slows down the macro. There is one issue with dates in column A of each sheet compared to the dates in column A of "ESI MONTHLY (get manually)" . If they don't match, the formula in the country sheet will return a blank.

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    05-30-2013
    Location
    England
    MS-Off Ver
    Excel Mac V16.68 and Excel 2019
    Posts
    33

    Re: Filling down a table to current month/date with VBA up to current month

    Hi Mumps,
    That's absolutely amazing, wow. Thankyou very much indeed, it worked like a charm.

    Just going to go through it and try to learn from it.

    Could I ask, in the original Macro I sent, it sort of worked, but some reason it was extending by many months and not up to the the current, why was it doing that (for my learning?) and why Excel was getting confused with that?

    Best Regards,
    Paul

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

    Re: Filling down a table to current month/date with VBA up to current month

    You are very welcome.
    in this line of code:
    Please Login or Register  to view this content.
    the "DaysInMonth" variable is equal to the number of days in the month so for February that would be 28. In this line of code:
    Please Login or Register  to view this content.
    LR1 would be equal to the LastRow + 28. If the last row was 400, LR1 would equal 428. Does that make sense?

  11. #11
    Registered User
    Join Date
    05-30-2013
    Location
    England
    MS-Off Ver
    Excel Mac V16.68 and Excel 2019
    Posts
    33

    Re: Filling down a table to current month/date with VBA up to current month

    Hi Mumps,
    I think it does. Will play around with it and try and learn even more. Really grateful for your script, and appreciate the time you put into it for me as it would have taken me hours (days?)

    Best Regards,
    Paul

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

    Re: Filling down a table to current month/date with VBA up to current month

    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. Replies: 1
    Last Post: 04-29-2016, 01:28 PM
  2. [SOLVED] Set PivotTable Filter to Current Day, Current Week, Current Month, or Current Year
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2014, 08:31 AM
  3. [SOLVED] Pick a cell containing current month actulas based what the current month is.
    By vanbasten007 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-10-2014, 01:17 AM
  4. VBA: Piviot table Month auto select based on current month?
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-24-2013, 08:45 AM
  5. VBA: Piviot table Month auto select based on current month?
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-21-2013, 07:18 AM
  6. Replies: 5
    Last Post: 10-04-2012, 07:06 AM
  7. [SOLVED] VBA to identify the current month and previous month based on system date
    By ravikumar00008 in forum Excel General
    Replies: 10
    Last Post: 07-26-2012, 10:04 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