Having trouble with macro formula referencing - offset? variable? help!

Hello again,

I am hopefully almost done with a rather large macro and my brain is melting. I have a macro where I have copied numerous other worksheets into a master and then inserted a column and now need that column to translate a Date/Time into a DD/MM/YYYY format for it's next stage. I've Attached an example of the worksheet.
I need this formula in Column C to the last row that has data =DATE(MID("B",1,4),MID("B",5,2),MID("B",7,2))
So, in other words cell C2 would be =DATE(MID(B2,1,4),MID(B2,5,2),MID(B2,7,2)), C3 would reference B3, etc...

I've looked through other posts and although I know I need to refer to the cell in Column B either through an offset or declaring it as a variable I just can't quite grasp what needs to be done.

I appreciate your help very much!




example.xlsm

Hi Tom,

Sorry, I guess I didn't explain very well. I need a macro to do this for me as yes, it is way too many rows and it needs to be repeated monthly as part of a macro that compiles alot of data from multiple worksheets. So, I know the formula, I just don't know the "macro language" to input into the VBA for it to do this for me.