Simple stock taking spreadsheet?

[dayav07]

Hi all..

I hope this is in the right section, forgive me if it is not.

I am trying to make a simple stock taking spreadsheet. In column A I will have all of my items. Then the next two items will be Balance, Stock Taken, Stock recieved.. This will be repeated 6 times (Monday to Saturday). But I would like to save this as a template. So the balances that appear in the last Balance column, I would like that to appear in the first balance column at the start of the new week.

So is it possible to have a button where when you click it, it will open up a new template with the end week balances as the first balance in the new week? And is it possible to have the default file name as the week ending date (Saturday's Date).

Thanks

[Guido Geurs]

This will copy the last Saterday in a new workbook and save it with the date of next saterday. (see attachment for demo)

Private Sub CommandButton1_Click()
Dim NEWBOOK As Workbook
Dim ARRCOPY As Variant
Dim NEXTWEEKdate As Date
Dim NEWFILEpath As Variant
    ARRCOPY = Range("Y2").Resize(Range("Y2").End(xlDown).Row, Range("Y2").End(xlToRight).Column)
    NEXTWEEKdate = Date + (14 - Weekday(Now))
    NEWFILEpath = ThisWorkbook.Path & "\" & "Allsales" & CStr(Replace(NEXTWEEKdate, "/", "-")) & ".xls"
    Set NEWBOOK = Workbooks.Add
    With NEWBOOK
        .Worksheets(1).Range("A1") = "Saterday"
        .Worksheets(1).Range("A2").Resize(UBound(ARRCOPY), UBound(ARRCOPY, 2)) = ARRCOPY
        .Title = "All Sales - " & NEXTWEEKdate
        .Subject = "Sales"
        .SaveAs Filename:=NEWFILEpath
    End With
End Sub