Automate date range entry

mikecox39

Board Regular
Joined
Mar 5, 2014
Messages
237
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-dynamic-array-formulas/?product_id=1212312&coupon_code=LAUNCH_DA
 
Last edited:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
890
Office Version
2010
Platform
Windows
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.
 

mikecox39

Board Regular
Joined
Mar 5, 2014
Messages
237
I'm sorry to be back but...:banghead:

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?

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

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
890
Office Version
2010
Platform
Windows
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.
 

mikecox39

Board Regular
Joined
Mar 5, 2014
Messages
237
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:

mikecox39

Board Regular
Joined
Mar 5, 2014
Messages
237
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?
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
890
Office Version
2010
Platform
Windows
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.
 

mikecox39

Board Regular
Joined
Mar 5, 2014
Messages
237
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 :ROFLMAO:

Cheers, (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,086
Messages
5,484,586
Members
407,457
Latest member
Zazu_0420

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top