Page 2 of 5 FirstFirst 1234 ... LastLast
Results 11 to 20 of 50

Thread: Automate date range entry

  1. #11
    Board Regular
    Join Date
    Mar 2013
    Posts
    801
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Mike, I'm no good with formulas so you'll need to start a new thread to get the formulas from the previous year for the budget sheet.

    As for creating and naming a new workbook for the upcoming fiscal year I would approach it differently.
    See if this would work for you. It's your workbook from post 10 emptied out and used as a template.
    Unfortunately we both need assistance to set up the formulas on the budget sheet.
    Hopefully deriving the previous fiscal year file name at the end of the macro will be of some use.

    https://app.box.com/s/ea17p9kzwcswl04oapf106ve1m7ezwh9

  2. #12
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Quote Originally Posted by NoSparks View Post
    Mike, I'm no good with formulas so you'll need to start a new thread to get the formulas from the previous year for the budget sheet.

    As for creating and naming a new workbook for the upcoming fiscal year I would approach it differently.
    See if this would work for you. It's your workbook from post 10 emptied out and used as a template.
    Unfortunately we both need assistance to set up the formulas on the budget sheet.
    Hopefully deriving the previous fiscal year file name at the end of the macro will be of some use.

    https://app.box.com/s/ea17p9kzwcswl04oapf106ve1m7ezwh9
    The template is a valid idea but the workbook is frequently tweaked during the year so using the previous method keeps the current workbook, with the tweaks.

    Thanks for your help with this. i learned a lot on the process. I will post a new question with regards formula techniques for pulling in data and name options.

  3. #13
    Board Regular
    Join Date
    Mar 2013
    Posts
    801
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Hello Mike

    Finished my Grandpa duties for the weekend and came back for another look at this.

    Think I now know how to deal with the formulas for the budget sheet but need you to verify before I incorporate into a completed
    'Get Ready for the New Year' macro.

    I copied the Budget sheet from your last linked file to a new workbook, have used columns P, Q, R, S, T and U as helper columns.
    This Budget sheet differs from the one in your original linked file by having Legal fees and Misc so I don't know what's actually in the file you're currently using.
    Have assumed the file you use now is MB18.xlsm and hard coded it into Q1 for test purposes, if that's wrong change it to actual name.

    This file needs to be in the same folder as MB18,
    running the 'Testing' macro should put the appropriate formulas into the column B cells and get the data without opening MB18.
    You need to check all the results, especially B9 and where it gets its data from.

    https://app.box.com/s/4wfi5lfyncu3dotql30sd00sibbydpdj

  4. #14
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Quote Originally Posted by NoSparks View Post
    Hello Mike

    Finished my Grandpa duties for the weekend and came back for another look at this.

    Think I now know how to deal with the formulas for the budget sheet but need you to verify before I incorporate into a completed
    'Get Ready for the New Year' macro.

    I copied the Budget sheet from your last linked file to a new workbook, have used columns P, Q, R, S, T and U as helper columns.
    This Budget sheet differs from the one in your original linked file by having Legal fees and Misc so I don't know what's actually in the file you're currently using.
    Have assumed the file you use now is MB18.xlsm and hard coded it into Q1 for test purposes, if that's wrong change it to actual name.

    This file needs to be in the same folder as MB18,
    running the 'Testing' macro should put the appropriate formulas into the column B cells and get the data without opening MB18.
    You need to check all the results, especially B9 and where it gets its data from.

    https://app.box.com/s/4wfi5lfyncu3dotql30sd00sibbydpdj
    Hey GramPa!

    I think I see your issue, the categories in the Master sheet don't match the categories in the Budget sheet; they are out of order and 2 are missing. I've reentered them so they match up and are in the same order. Will that fix the issue?

    I've attached the updated this years budget file, MB18. I added a module with the clear data macro you so kindly provided, with a couple modifications. It's works perfectly! Where should that be added to the new code, so it all works together?

    https://app.box.com/s/l9ejhyrxoamweuvf649dg9le58iu21zc

  5. #15
    Board Regular
    Join Date
    Mar 2013
    Posts
    801
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Okay, have changed your Budget sheet to be what I think you want.
    Removed one Taxes row and one of the Landscape rows.
    Items are ordered as they appear on Master sheet, although that was not critical.
    The important thing is columns T & U on the Budget sheet as these are the paired cells of where the formula goes and what it's linked to,
    Column S is just for clarity on the T & U cells, these 3 columns were manual input.
    Cells P1, Q1 and R1 are populated by the VBA and used in the formulas for path to the 'old' Master sheet.

    Have removed the BackupBudget module as its file names are/will be wrong.
    Replaced the Clear_Data module with NewYearSetUp module.

    Hopefully this does the job.

    https://app.box.com/s/dabodxk3m7rtcspc4kads37elb84ckcc

  6. #16
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Quote Originally Posted by NoSparks View Post
    Okay, have changed your Budget sheet to be what I think you want.
    Removed one Taxes row and one of the Landscape rows.
    Items are ordered as they appear on Master sheet, although that was not critical.
    The important thing is columns T & U on the Budget sheet as these are the paired cells of where the formula goes and what it's linked to,
    Column S is just for clarity on the T & U cells, these 3 columns were manual input.
    Cells P1, Q1 and R1 are populated by the VBA and used in the formulas for path to the 'old' Master sheet.

    Have removed the BackupBudget module as its file names are/will be wrong.
    Replaced the Clear_Data module with NewYearSetUp module.

    Hopefully this does the job.

    https://app.box.com/s/dabodxk3m7rtcspc4kads37elb84ckcc
    Ok, got it.

    Now I need some time to sort it out. Which means I am about to open the macro and study it, and your notes, in an effort to appreciate how awesome it is, and to understand how it works. That will will take some time and most certainly generate questions, which I hope you would be willing to indulge.

    As I look at the Budget sheet I notice that column B has manual entries, with this years data. I expect when the macro runs on the last day of the month it will get the numbers from last years Budget sheet and plug them into MB19, that it will create. I'm guessing Column T has something to do with what will end up in MB19's Column B.

    Thank-you for doing this, I hope you won't rue the day you got involved

  7. #17
    Board Regular
    Join Date
    Mar 2013
    Posts
    801
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    As I look at the Budget sheet I notice that column B has manual entries, with this years data
    Those are numbers that existed on that sheet in the file you last supplied. I don't know where they came from, they are under Fiscal 7/1/2016 and will get replaced with data from MB18 Master. But the saved MB18 will have them on that Budget sheet. You could delete them before running the macro if that's an issue.

    I'm guessing Column T has something to do with what will end up in MB19's Column B.
    the macro loops through column T for the cell to put the formula in, and the adjacent cell in column U tells the macro which cell the formula is to use on the previous year Master sheet.

    I hope you won't rue the day you got involved
    Me too, actually I just hope we get it working for you.

  8. #18
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Quote Originally Posted by NoSparks View Post
    Those are numbers that existed on that sheet in the file you last supplied. I don't know where they came from, they are under Fiscal 7/1/2016 and will get replaced with data from MB18 Master. But the saved MB18 will have them on that Budget sheet. You could delete them before running the macro if that's an issue.

    the macro loops through column T for the cell to put the formula in, and the adjacent cell in column U tells the macro which cell the formula is to use on the previous year Master sheet.


    Me too, actually I just hope we get it working for you.
    Thanks for the clarification.

    I will try my best to work this out on my own, I don't want to wear out my welcome (-:

  9. #19
    Board Regular mikecox39's Avatar
    Join Date
    Mar 2014
    Location
    San Diego
    Posts
    237
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    I just ran the macro you created for me and it went will until it popped up a d.docs. window requesting a password. I tried my MS Live password but it didn't work so I don't know what PW it wants. I'm also concerned that the person taking over from my will have the same problem. Anyway to avoid that pop up?

  10. #20
    Board Regular
    Join Date
    Mar 2013
    Posts
    801
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    I have no idea what d.docs is and doubt the request is coming from Excel as nothing I worked with has any password.

    A Google search for d.docs indicates it's something to do with Microsoft's OneDrive, which I don't use so
    sorry, I'm afraid I can't help with that.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •