Nov 08 2020 03:37 PM
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
Nov 08 2020 05:32 PM
Nov 08 2020 08:39 PM
@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
Nov 08 2020 11:43 PM
Nov 09 2020 01:25 AM
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)
Nov 09 2020 02:17 AM
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.
Nov 09 2020 04:44 PM
@Wyn Hopkins Thank you and let me know if you don't understand and can't put the screen shots together with the spreadsheet. The 10-30-20 is the beginning day, it carries over to 10-31-20 where the grayed in cells are inputted each day with new numbers. Then the bottom 2 cells of numbers transfers over to the new sheet/day and then new numbers are put in there. The example of that is the 11-02-20.
Nov 09 2020 05:34 PM
Hi @tpadkins
My approach to this would be to have a single input sheet with the date in column A and just keep running the table down the page.
Your reporting can then be done on a different page using a Pivot Table or something like SUMIFS / INDEX MATCH / XLOOKUP
Splitting the inputs onto daily pages seems to overcomplicate things (from an outsider perspective)
Nov 10 2020 01:46 AM
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.