Hi,

I hope someone can help.
I have a spreadsheet i use to record mileage and dates. I have a column with dates A2:A366 and each date is in the format
1st January
2nd January
3rdJanuary
etc down to the end of December. Then there are columns (currently B:E) which hold annual mileage. So As I do the miles, I enter the value in to the cell for the correct date and year. So mileage on 1st January 2013 goes into cell E2, 5th January 2012 is in D6, etc.
The Cells are arranged in a table, and I have various functions and macros running in VBA to use the data as I enter it in.
I want to create a second table, on a different sheet. This table will have dates in the left hand column, and then a column for weekly mileage and another for weekend mileage. I will create code which will take values from the first table and populate the second and third column in the second table with calculated values for the miles covered in a week and a weekend, simple maths calculations, which I can manage.

What I would like to do is create a macro/function that will populate the first column of the new table with the date of every Sunday of the current year, in the format 6th January, 13th January, 20th January etc right through until the end of December. I would like the format of the Date column in the second table to match the format in the thirst so that once I have a selected date in the second table, I can loop through the entries in the first table until the two match. I canthen select values in the miles columns to do the various calculations.
I hope that all makes sense??
I’m struggling with the macro/function that enters the Date of each Sunday of the current year into the 2nd Table. That’s the first step. The second is to then convert it into the same format as the first table (1st January, Second January etc.

Please can anyone advise? I’ve tried all sorts using weekday and various other things, but I can’t get it. I’ve tried ranges and all sorts but I can’t get the hang of it. I’ve done basic VBA but I’m not very experienced with it.
Many Thanks
John