Copy a spread sheet with reference to the "next" sheet

[markrohde57]

I have a weekly report that accumulates numbers YTD.

Easy example:

Sheet 1 (name "week 1")
A1 = Sales for week 1
B1 = A1

Sheet 2 (name "week 2")
A1= Sales for week 2
B1= Week2!A1+Week1!B1

Sheet 3 (name = "week 3")
A1= Sales for week 3
B1= Week3!A1+Week 2!B1

Each sheet is a very full sheet of many similar formulas

I create a copy of the newest sheet and rename it the new week. The sheet tab order goes down from left to right (ie- Week 4 , Week 3, Week 3 , week 1)

The formula in B1 does not use a dynamic formula (the sheet right before the current sheet) but rather puts in the name reference as the same one in the original sheet -

If I were to copy and create a Week 4, the formula in B1 would read B1=Week4!A1+Week 2!B1 when it should have referred to Week 3.

Probably a silly question, but I just can't figure out how to copy a sheet correctly.

Mark

 

[venkat1926]

try this macro

week1 week2 week3 ........sheets are in order.

Sub test()
Dim i As Integer
Worksheets("week" & 1).Activate
Range("B1") = Range("A1")
For i = 2 To Worksheets.Count
With Worksheets("week" & i)
.Range("B1") = .Range("A1") + Worksheets("week" & i - 1).Range("B1")
End With
Next i


End Sub