Nov 08 2020 03:37 PM
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 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.
I know that I know nothing (Socrates)
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
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.