Locking up worksheets

[KentKHI]

Hi folks!

I have been building an estimating workbook with everyones help, and it has been working great. I now have a new challange though.

I have my workbook setup to connect to a seperate database workbook that opens up each time I open my estimate workbook. What I want to do is protect certain sheets in my estimate from changing after I present the estimate.

Here is what I see as the problems:

1. I want to be able to have the database open while the estimate is open.

2. I have formulas in my estimate that equal amounts calculated from the database.

3. I want to still be able to use some of the worksheets in my workbook that are not tied to the database.

Here is my first idea:

First I would save the workbook as a copy (as to keep the original to alter it if need be)

Then, I would like to take the estimate sheets that tie to the
database and set every cell equal to its current value (I mean what it is currently displaying??)

This will get rid of any formulas and allow me to use the worksheet as a non-changing read-only.

Is this possible, will it work, and any ideas on the code to perform this would be great.

I appreciate any input!

 

[Atom]

After you gave the estimate could you run a macro to copy and paste the values into a new sheet?

 

[Tazguy37]

Then, I would like to take the estimate sheets that tie to the
database and set every cell equal to its current value (I mean what it is currently displaying??)

This will get rid of any formulas and allow me to use the worksheet as a non-changing read-only.

Something like this:

    With ActiveSheet.UsedRange
        .Copy
        .PasteSpecial xlValues
    End With
    Application.CutCopyMode = False

Shoudl do that part of it. If you need to make a new sheet in there someplace, so that's where the values go, post back and we can take care of that. HTH