Can this VBA be simplified

Hi and thanks for looking at my thread.

I have the following VBA and it changes the dates on different sheets when the date is changed on the "DATE" sheet

the VBA does the job just wondering if it can be simplified.
heres the code

Please Login or Register  to view this content.

many thanks

Toonies

Hi thanks for your suggestion, though it throughs up errors

Run-time error '1004':

Select method of Worksheet class failed

this is the problem line

Please Login or Register  to view this content.

also the date should change by 1 in each cell within the range on each sheet

ie: each sheet is 7 days ahead of the previous sheet

I'm open to any further ideas

Untested

Please Login or Register  to view this content.

Hi AB33 your code is like my original code that I am looking to reduce.

mike7592 I amended your code to the following

Please Login or Register  to view this content.

also the date should change by 1 in each cell within the range on each sheet (ie: each sheet starts 7 days ahead of the previous sheet)

but however with the amended code it is putting the same 7 dates on each sheet.

here is a copy of the spreadsheet

Demo 1a.xls

You can simplify this section:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

And this section:

Please Login or Register  to view this content.

Can be reduced to:

Please Login or Register  to view this content.

Hi Andrew-R

I get a Run Time error '13'

and this part of the code is highlighted

Please Login or Register  to view this content.

Sorry, the final "*" should be an "&"

Ok now I have got that to work but it still populates each sheet with the same 7 dates

in my original code it populated each sheet with a with 7 dates each sheet dates 1 week ahead of the previous sheets starting date

hope I'm explaining myself correctly

Hi I'm still open to ideas

Watersev now thats a cool bit of coding many thanks it works a treat

could you explain how it actually works

thanks

Toonies

loop from Sheet "Sheet2" till sheets count, for each sheet an array is filled with formulas and pasted to the sheet

Many thanks