Not typical copy and paste questions

[tpadkins]

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

[#2]

Hi, it sounds like quite a complex series of steps that would require you to share some screenshots and examples for us to understand and advise properly. However, on first impressions it sounds like you'd need some custom VBA code written to achieve what you would like to do.

[#3]

@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

[#4]

These screen shots are not enough to find & fix the issue,, if possible please share the workbook with us, and let us examine!!

[#5]

@tpadkins 

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)

[#6]

@tpadkins 

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.

[#7]

@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.

[#8]

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)

[#9]

@tpadkins 

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.