Update a cell from another cell on 1st every month

[Stevensjs]

[FONT=&quot]Hi[/FONT]
[FONT=&quot]I am trying to automatically copy the percentage in a cell to a cell on a different worksheet on the 1st day of every month to track the progress of a task.[/FONT]
[FONT=&quot]For example I update cell a1 each week and the percentage increases but on the 1st of each month I would like this percentage to automatically copy into a cell on a different sheet.[/FONT]
[FONT=&quot]Hope this makes sense.[/FONT]
[FONT=&quot]Is this possible.[/FONT]
[FONT=&quot]Any help much appreciated.[/FONT]
[FONT=&quot]By: Steven Sexton[/FONT]

 

[mumps]

Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Each time you open the file, it will check the current date to see if it is the first day of the month and if it is, A1 of Sheet1 will be copied to A1 of Sheet2. Change the sheet names and ranges to suit your needs. Close the window to return to your sheet.

Private Sub Workbook_Open()
    If Day(Sheets("Sheet1").Range("A1")) = 1 Then
        Sheets("Sheet2").Range("A1") = Sheets("Sheet1").Range("A1")
    End If
End Sub

 

[Stevensjs]

Hi
That’s excellent thank you but I don’t think I was quite clear.
For example on 1st July I want the data in a1 sheet 1 to update into sheet 2 cell a2 but then on 1st August I want the data in the original cell to update into sheet 2 cell a3 and so on.
Is this possible
Sorry for confusion and thanks for your help.

 

[mumps]

Try:

Private Sub Workbook_Open()
    If Day(Sheets("Sheet1").Range("A1")) = 1 Then
        Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Offset(1, 0) = Sheets("Sheet1").Range("A1")
    End If
End Sub