Page 5 of 5 FirstFirst ... 345
Results 41 to 50 of 50

Thread: Automate date range entry

  1. #41
    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

    Thanks for the Wait code, I will add it to my list of favorites.

    I plan tol try it but I doubt I will need it because it's easier to just run the macro on my HD then save a copy in both places. Once it's been updated I can work with the new file from either place.

    I did get a response from my post about the cloud that wasn't very encouraging.

    On a completely unrelated topic (I suppose I should create a new post) I just got this promo from the instructor of a course I'm taking. Do you know what this new XL for 365 is all about? I have Office 365 on my machine so I'm guessing it's applicable but is it something everyone should be learning? Will it really be better or "easier" and will it replace the current version sometime in the future?

    https://courses.xelplus.com/p/excel-...code=LAUNCH_DA
    Last edited by mikecox39; Jul 11th, 2019 at 03:18 PM.

  2. #42
    Board Regular
    Join Date
    Mar 2013
    Posts
    818
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Running from the HD you could go back to about post 15 for a working macro, that other stuff was added as an attempt to track down what was going on.

    Regarding your unrelated topic, no idea what that's about.
    With the amount of activity there is on all the different Excel forums, I doubt there will be much change in the foreseeable future.*


    * I have been wrong before.

  3. #43
    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

    Yes, I've saved all the iterations of the macro. Thanks

  4. #44
    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'm sorry to be back but...

    I've been making entries into my updated MB19 spreadsheet and the Data from the Details sheet isn't getting added to the Master sheet.

    The formula for getting the amounts from the details page to add up and be added Month cell of the Category on the Master sheet is not working now. It is working on MB18, the sheet used to update MB19.

    This is a very complicated formula that truly baffles me. I see a [date] in the code but I don't know how to interpret it. Could that be the place where things have gone wrong? Should this code have been updated as well, to make it relevant to the updated dates on the Sheets?

    =SUMIFS(tbl_Details[[#All],[Amount]:[Amount]],tbl_Details[[#All],[Account]:[Account]],Master!$A30,tbl_Details[[#All],[Date]:[Date]],">="&Master!D$1,tbl_Details[[#All],[Date]:[Date]],"<="&EOMONTH(Master!D$1,0))
    https://app.box.com/s/l9ejhyrxoamweuvf649dg9le58iu21zc

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

    Default Re: Automate date range entry

    When you made the " Details orig" sheet, it really threw a wrench in the works.
    tbl_Details hasn't moved, it still on the same sheet, just not the one you're now entering the details on.

    Notice in the section of code that clears where you are now entering details, you had to change the table name ?

    The simplest fix will be to make a copy of what is now "Details orig" and on the copy convert the table to a standard range.
    Clear the table on "Details orig" ready for new details.
    Delete what is now "Details" and rename "Details orig" to "Details"

    Let me know if I should do it and post the workbook.

  6. #46
    Board Regular
    Join Date
    Mar 2013
    Posts
    818
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry


  7. #47
    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
    When you made the " Details orig" sheet, it really threw a wrench in the works.
    tbl_Details hasn't moved, it still on the same sheet, just not the one you're now entering the details on.
    Before we go any further let me address this sheet.

    I'm not at all sure why that sheet got added because there is no reason for it to be there!

    I tried deleting it but it didn't help.

    It's past my bedtime so in the morning I will try another run with the macro, and create another "update", making sure this sheet doesn't get on the update. I'll let you know what happens when it's done.

    Opps, I just saw your second post. I'll take a look at it in the morning.
    Last edited by mikecox39; Jul 19th, 2019 at 04:26 AM.

  8. #48
    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

    Ok, I couldn't wait

    Thanks! It looks like you've fixed it.

    Would it mess anything up if I just deleted the "Copy of 2018 Details" sheet, since it shouldn't even be in the workbook?

  9. #49
    Board Regular
    Join Date
    Mar 2013
    Posts
    818
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Automate date range entry

    Deleting it that won't matter at all, it's just a copy of that ' Details orig' sheet with the table on the copy converted to a range.
    The sheet was renamed to make sure it wouldn't be confused for anything else.
    If you click the little drop down arrow on the formula bar on the left where the active cell address is,
    you'll see you're back to only your original tables.


    fyi
    The way I discovered the table issue was to click into Master sheet C25, then
    from the Formulas ribbon, Formula Auditing section, Evaluate Formula.
    This is where you can see how Excel handles the formula each bit at a time and it showed the sheet of the table.

  10. #50
    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
    Formula Auditing section, Evaluate Formula.
    This is where you can see how Excel handles the formula each bit at a time and it showed the sheet of the table.
    Great! Thanks again and thanks for for showing me this.

    Now I can study that formula, which was developed by someone like you, on this very forum! Maybe I will finally begin to understand how it works.

    fyi, I am currently working through a very long course in VBA. I'm currently in section 6, dealing with Error Handling so this is timely.

    This course has 20 sections, divided into 7-12 sub-sections with 15-20 minute running times but which take me at least 90 minutes to process, going step-by-step! I may die of old age before I finish it

    Cheers,

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
  •