VBA Macro - sometimes it works, sometimes it doesn't

[EuanM28]

Hello!

I have a little issue here I am very confused with. I have coded a 4 subroutines that do the following:

1. Merge - Import specific worksheets from other workbook files and import them into a new one
2. Clean-up - Removes any unnecessary fields from both new worksheets
3. Model - Remodels one of the imported worksheets (Worksheet 1), including changing column arrangement and adding new columns with formatting and formulas for those columns.
4. Transform - Remodels the other imported worksheet (Worksheet 2), changes column header names, adds several new columns with formatting and formula's, a lot of VLOOKUPs from the last worksheet (Worksheet 1)

Now, all of my code works perfectly. However, the issue I am having is with the 4th and final subroutine (Transform). When implementing and testing my code, I would separate each subroutine to 4 separate buttons to click. The first 3 work exactly how I want them too. However, the 4th one seems to have an issue. All of the formatting works fine, but the VLOOKUPS and other formulas for newly added and formatted columns sometimes show their expected data and sometimes they do not (Instead they show blank cells).

When I gave my laptop a minute after it had finished the 3rd routine, the 4th and final button click would always work, showing the expected results. With this knowledge, I decided to add the following code at the end of my third subroutine and at the beginning of my 4th "Application.Wait (Now + TimeValue("00:20:00"))". I came to the conclusion that, oh! it must just need a few seconds to settle, as I am using VLOOKUPS etc to another worksheet that has JUST been updated and changed etc. However, this did not work. Simply running subroutines 1-3 and even giving a few seconds after the 3rd one loads to load the final one works.

Very confused, any ideas?

Note: The columns that I expect to have data just come up with BLANK cells, nothing in them. But, everything else the 4th subroutine does works perfectly.

This is my first ever question so apologies if anything is confused or worded badly!

Regards,
Euan

 

[EuanM28]

Like i said if you just use Range (rather than for example Sheets("Sheet1").Range) then excel will use the active sheet at the time the line with Range executes. That may or may not be the sheet you need it to be. It can be variable depending on what sheet you was on when it all started. If you use buttons though they always execute from the same starting point so you wouldnt have experienced the same variability.

Awh I see, great help! Thank you so much, apologies, I havent used VBA for 6+ years so getting back into it.