If you put this formula into A2 on sheet 2:
when you copy it to sheet 3, cell A2, you will only have to change the 1 to 2
There are ways of creating worksheet/cell addresses using formulas but IMHO it's going to be more time consuming than just making a single edit each week.
I did create a custom function to do this:
Public Function LastLieu(CellAddress As String)
Dim strWsThis As String
Dim strWsLast As String
'get this worksheets name
strWsThis = Application.ThisCell.Worksheet.Name
'convert it to last worksheet name based on Sheet 2 -> Sheet 1
strWsLast = "Sheet " & Format(CInt(Right(strWsThis, Len(strWsThis) _
- InStr(1, "Sheet ", strWsThis) - 5)) - 1, "#0")
'get value from cell passed to this function in last worksheet
LastLieu = Worksheets(strWsLast).Range(CellAddress).Value
This would have to be entered in a new standard module in the visual basic section of the workbook.
In cell A2 on each workbook enter =LastLieu("A2")
The function will fail if you name your worksheets differently.
You used "sheet 1" which is not the default naming for Excel worksheets.
If you used "Sheet1" etc. which is the Excel default format the custom function will need to be edited.