VBA Clearing Data from Visible Cells Only & Copying Worksheet with Protection in Place

I am using all those cells as they transfer across to another sheet which works out wages.
I could make a more complicated sheet I suppose where totals are worked out only on the wages sheet.

Indeed, and your suggestion lead me to taking all those sells out and shifting them all to one side. They can all be hidden now and I can delete the data in all the other cells without deleting the formulas.

The rest of the cells I dont want changed should easily be locked out now as they dont need to be hidden.

Thanks

ANy thoughts on the issue of cutting and pasting a master sheet to a new sheet and having the protections stay in place?

Hi

Have you thought having the cut and paste being done by vba with a button for users that sets up a input spreadsheet for users? This would retain the master.
How do you propose data to transfer from users to the final master and subsequently the payroll input?

It would be wise to have a master template that is not used at all for backup.

Cheers

Unless the spreadsheet could be printed out in the layout in the file it wouldnt be any use.

The user (9 times out of 10 its me, but occasionally someone else) will enter the data on the rota, another workbook with then reference the hours for each employee each day and do its calculations to produce how much money they are owed and how much holiday they have accrued. The only manual input on that sheet should be how many hours holiday an employee might have taken.

There are some fiddly bits that you will have to do manually though, such as cut off dates for pay roll, and changing sheet locations in formulas. I have no clue where to start in making that automatic - probably not possible, but dont mind spending a day setting up the workbooks for an entire year.

I'm reasonably happy where I am at at the moment, afew issues like holiday pay and split shifts to think through but overall i'd like to make it as simple to use as possible. Cell protection is one such measure I would like to use at some point.

YOu can but I have no clue what VBA. If it will help me I will take a look. Thanks

Hi

Excel VBA is a tool available to automate repetitive tasks such as deleting multiple columns of data leaving protected cells alone.

In this instance I think I have achieved your aim. Deleting unprotected cells through recording a macro to do the task. I have added a button so that using this spreadsheet or a copy of it you can run the job whenever you want. What I have done is crude but it works. A more sophisticated approach can deliver more.

Withhout being critical because I firmly promote learning being self taught therefore probably not as good as some but as good as a lot, read any introduction to Excel to point you in a direction. Macro are not the b all and end all but can help.

I have attached a revised spreadsheet with the code below if you are interested.

ub Macro1()
'
' Macro1 Macro
'

'
    Range("B5:C32").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=21
    Range("B34:C39").Select
    Selection.ClearContents
    Selection.ClearContents
    Range("B41:C42").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-42
    Range("E5:F32").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=28
    Range("E34:F39").Select
    Selection.ClearContents
    Range("E41:F42").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-42
    Range("H5:I32").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=21
    Range("H34:I39").Select
    Selection.ClearContents
    Range("H41:I42").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-35
    Range("K5:L32").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=21
    Range("K34:L39").Select
    Selection.ClearContents
    Range("K41:L42").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-28
    Range("N5:O32").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=21
    Range("N34:O39").Select
    Selection.ClearContents
    Range("N41:O42").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-35
    Range("Q5:R32").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=14
    Range("Q34:R39").Select
    Selection.ClearContents
    Range("Q41:R42").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll ToRight:=5
    ActiveWindow.SmallScroll Down:=-28
    Range("T5:U32").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=14
    Range("T34:U39").Select
    Selection.ClearContents
    Range("T41:U42").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-35
    Range("AA15").Select
    ActiveCell.FormulaR1C1 = "Finished"
    Range("AA17").Select
End Sub

PS it took longer to write the reply than record the macro. Please respond if this helps and hopefully I can provide some further assistance

Cheers