Refresh and formulas not populating on a worksheet activate consistently

I have the most baffling problem and have spent hours trying to figure this out without any luck. Hoping someone here might have some insight.

My spreadsheet is HUGE so I'm just going to try to explain best I can and attach code for you. If I need to attach a small version I can try to do that if needed (just thought I'd start with the code for your smartie pants in here )

I have a quote book workbook I've built. The contractor can build rooms with various specs and pricing and then it updates a quote sheet (ws in the wb). The specs from various rooms are pulled together via a power query resulting in an appended table (found in ws Append1). I want the quote sheet to update automatically when activated. I have the following code.

Please Login or Register  to view this content.

When you click on the quote sheet this doesn't always update properly. It will only show some of the append table entries - for example starting in Row 4 or 5 from the append table. If I "step into" the macro it works (making this hard to troubleshoot), but if I just click on the sheet it doesn't always populate correctly. However, if I click on another sheet and go back to the quote sheet it works every time. So the work around is leaving the quote sheet by clicking another tab and going back, but I'm trying to avoid having to do this every time. Any ideas what I'm doing wrong or if there is code I can add to solidify this?

Thanks!

HI Greg: Thank you for the feedback - much appreciated since I'm still trying to learn how to improve my coding language. I'm going to look for a course in the next couple of months to help - but in the meantime comments like yours are very helpful.

I "assume" that the code is stored in a worksheet class module since in the VBA screen the worksheet is selected on the left and then above the code I see worksheet and activate in the pull downs. I'm not sure why it didn't capitalize but I have corrected that in case it makes a difference - very weird - but good observation.

Below is the code I have now but it's made no difference in the behaviour. I even threw in an extra "refresh" just to try but that makes no difference and is probably not required. But I'm trying different things to see what happens.

Please Login or Register  to view this content.

It's the most puzzling thing because I feel like the order of this should work. I should also clarify that the append table is also updating properly on the first refresh so I don't think it's my query since the resulting table is what I want. If I "step into" the code it works every time. And if I leave the tab and click on it a second time it works every time. Just wish we could avoid having to "double activate" the sheet - so to speak.

Thanks,
Stef.

Hi again,

Thanks for all of that information.

Is there any chance that you could post a sample of your workbook here? We don't need to see any sensitive data, but it's going to be a bit easier to work out what's (not?) happening "in the workbook" rather than "in the head".

Regards,

Greg M

HI Greg: I've tried to strip away a bunch of data, etc. and leave enough so you can see what I'm trying to do with this. Hopefully file size works. Thanks.

Hi again Stef,

I'm just sure I'm missing something here, but my question is "Why do you need to re-enter the formulas each time?" As far as I can see, the formulas are not dynamic - i.e. the same formulas are being entered each time, and it is only the formula evaluations which might change.

Also, the formulas in Rows 19:33 are being duplicated in Rows 34:48 - is there a reason for this?

Regards,

Greg M

Ah, crap. I deleted a bunch of rows at the top of the Quote Sheet to shrink this file down and forgot that my ranges start in row 34 - hence the duplication that shouldn't be there. I've re-inserted blank rows so this works like my main file would - details should start in row 34.

Why I need to have the formulas re-created every time is that the way this tool works is that the table the data is being pulled from is dynamic via power query. So as the report changes this needs to recalculate because table rows are deleted or added to depending on selections. The way it works is:

1. Rooms that are being built are selected in Select Rooms tab. There will be 20 rooms but this sheet only has 2 incomplete ones (again for file size I deleted a bunch of stuff)
2. Using Room 1 as an example, the contractor then enters all the specs on the "Rm 1" tab.
3. Specs are updated in a table that will be used to calculate the total cost of the project. All the rooms update to tables via power query and into the append1 tab/table (pulls in pricing, etc).
4. The quote sheet is updated based on the append1 table contents and calculates a project cost based on all the line items.

I've saved this with some data in it, but if you clear Room 1 and enter new data you can see that the resetting of the Quote sheet doesn't always work as I would expect it to.

Hopefully that helps explain the what and why of what's going on.

Thanks.

You are BRILLIANT!!! Thank you SO MUCH for the time you took to help me. Some of this is above my skill level but I'm going to take time to digest your code in detail later so I fully understand. I want to learn these more advanced things and hope to find a suitable course to get me there. There is always so much more to learn with Excel.

Much appreciated.
Stef.

Hi again Stef,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

You're welcome - glad I was able to help.

There is always so much more to learn with Excel.

Yes indeed! And the great thing is that the more you know about Excel, the more the walls are that available to bang your head against when you're trying to solve a problem!

Best regards,

Greg M