I have a date in E1 on worksheet, how do i auto add date plus 7days on the other 51 worksheets in my workboo?

Question

i dont want to have to go into every 52 worksheets and put in individual dates a week difference

Answer 1

If its a standard decimal date, then

=E1+7

adds a week

Answer 2

This VBA macro will add 7 days to the date in E1 on the other 51 sheets without changing the date in E1 on Sheet1.

If your starting sheet is not Sheet1, you will have to modify the macro to your sheet reference. To do that, just change 'Sheet1' in both places to the name of your starting sheet.  Make sure it is between the "" marks.

Open your workbook

Copy this macro, modified if necessary first, to the clipboard.

Sub Set_Date_E1()

Dim E1_Date

E1_Date = Sheets("Sheet1").Range("E1").Value + 7

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

ws.Select

If ActiveSheet.Name <> "Sheet1" Then

Range("E1").Value = E1_Date

End If

Next

End Sub

Next, press ALT + F11

Insert  >  Module

Paste the macro into the Module space to the right.

Close back to Excel.

Go to Tools  >  Macro  >  Macros

Click on the macro to highlight it, if it is not already highlighted.

Click:  Options

Select a letter to be used as a keyboard shortcut.

Close back to Excel.

Press CTRL + your shortcut letter to run the macro.  You can run the macro this way any time you like.  You can also run it from any sheet in your workbook.  It does not have to be called from your starting sheet.