Is there a way to have a spreadsheet, that is set up already with formulas and some cells protected, and then when I copy to a new page, it transfers the data, clears the unprotected cells and carries over the formulas so that all I have to do is hit copy and the date is changed and invoice numbers are changed, etc
Wyn Hopkins I have attached one that is filled out and the other one that has the comments written on it so it will show what I want to see if can be done. Thank you
With the permission of all involved, here is a small proposal for a solution with VBA .
(not tested, but should work)
Sub SheetClone()
Dim ws As Worksheet, wsClone As Worksheet, r as Range, col as range
Set ws = Sheets(1)
With ws
.Copy After:=ws
Set wsClone = Sheets(ws.Index + 1)
wsClone.Name = ws.Name & "_copy"
For Each col In .UsedRange.Columns
wsClone.Columns(col.Column).ColumnWidth = col.ColumnWidth
Next
For Each r In .UsedRange.Rows
wsClone.Rows(r.Row).RowHeight = r.RowHeight
Next
End With
End Sub
I would be happy to know if I could help.
Nikolino
I know that I know nothing (Socrates)
Could you simply copy the worksheet?
Note: If you use defined names (I never reference data other than by name), then it is best to use names scoped to the sheet.
If you wish to retain the format of a daily form (as opposed to a database table with the ability to generate a report for any given day) then I think that the best way forward is to have an unpopulated sheet as a template (possibly hidden) which is cloned to produce a new form for each day.
VBA could be used to create and name the new sheet, as well as any Tables you choose to introduce, and would link the volumes brought forward to the amounts carried forward from the previous sheet.
