Read Only view of a worksheet

[blacktusk]

I have a worksheet that is using VBA to generate a report based on SQL Server data. The worksheet displays the data and dynamically calculates the print area. The user can popup a calendar to select a specific day, and rerun the report.

However, there are a number of Heading cells and calculated cells in the header area that I do not want modified by the user. I cannot protect the worksheet as the report will no longer generate, and I cant use Range unprotect cells as the dynamic report data size is unknown.

I was hoping to be able to create another worksheet that is a view only of the formatted report data, however, if I use the =Report!xy syntax in all the cells of the view worksheet I will get data in cells that have no content in the source worksheet.

Is there a relatively simple method using VBA that could be used to dynamically replicate the formatted report data into another worksheet?
Or, alternatively a way to protect only the header section of my worksheet?

[Bob Phillips]

Why not protect the sheet, and unprotect it just before genearting the data, and reset protection afterwards

[maxhayden]

I have an idea... it may be wrong though.

If the main sheet you are refering to is a 'Template', you could hide the sheet, and each time you import your data, it fills a copy of the hidden template.

Using -->


Public Sub HiddenTemplate()
With Worksheets("Template")
.Visible = xlSheetVisible
.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "InputName"
.Visible = xlSheetVeryHidden
End With
End Sub


You can integrate this with the data import macro to fill into the copied sheet.

[blacktusk]

Thankyou XLD, I tried what you suggested and that is perfect. If the sheet is protected I unprotect to write the report and reprotect! Presto...:beerchug: