I am creating automatic charts to be updated on a weekly basis by simply
recording the macros while creating them in Excel and then run the macro
every week to update the charts.

I have a range that can be either week 1-52 (full year) or rolling over 52
weeks (from August to August for example).

How do i set the range based on current date i.e. I do not want to show the
remaining weeks of the year empty as it affect trend lines??