Hi all.
I've trawled the internet for an answer to this issue to no avail. Although I'm sure it's probably a rookie mistake!
Background: I've developed a spreadsheet which takes data from an access database, and produces a calendar of upcoming implementations in our department. In theory, very simple. The calendar is on a separate sheet and is very simple - works perfectly, with dates on the X axis and implementation names on the Y. The sheet checks the imported data and using if statements, displays the start and end times of the implementation by displaying S and E in the relevant date cell, then conditional formatting. So far so good.
HOWEVER, when I refresh the data source, the previous last row in the "data" sheet must get deleted and recreated, because I get a row missing in my calendar. Let's say the last row on the data sheet was A7. After refreshing the data, the row in the calendar referencing A7 disappears, it goes straight from 6 to 7.
All well and good, I thought it was probably simply an issue with the data refreshing functionality built in, so I built a macro which deletes the formulas from the calendar sheet, refreshes the data using a simple ActiveWorkbook.RefreshAll, then reintroduces the formulas into the calendar. However, the issue of the missing row persists when the macro is run. When I step through the code it's fine. I thought the import might be taking more time than the macro, so I added a wait of 10 seconds (way more than it needs), but the issue continued.
I then thought it might be because the sub which refreshes the data might need to end before the format sub (maybe it was doing it in parallel), so I split the macro into 2, and ran them sequentially from a third sub. Still happens.
I've tried everything I can think of. My only current workaround is to split them into 2 separate buttons, which works perfectly, but it's a bit silly considering it's such a simple thing to achieve!
I've even tried running the format macro multiple times after the import, but it doesn't work, however running the whole suite twice (i.e. 2 clicks) does work.
I'm stumped!
Help!
Bookmarks