Macro that unprotects worksheets, transfers data, then reprotects

I am not fluent in visual basic. For the most part I record operations and
then cut/paste them to my main macro.

Right now I am working with a workbook with 4 sheets.

Sheet1 - All locked except cells B5-B8 and B11-B17
- Protected

Sheet2 - All locked
- Protected

Sheet3 - All locked
- Protected

Sheet4 - All locked
- Protected

When I engage the macro the first operation is -->

Sheets("Sheet1").Select
ActiveSheet.Unprotect
Sheets("Sheet2").Select
ActiveSheet.Unprotect
Sheets("Sheet3").Select
ActiveSheet.Unprotect
Sheets("Sheet4").Select
ActiveSheet.Unprotect

This works.

The next operation in the macro is to take values on sheet 1 and transfer
them to the other sheets. Here is an example of the data stored on sheet 1
in cell B5 -->


Sheets("Sheet1").Select
Range("B5").Select
Selection.Copy
Sheets("Sheet2").Select
Range("D7").Select
ActiveSheet.Paste
Sheets("Sheet3").Select
Range("B13").Select
ActiveSheet.Paste

After all the transfering of data from Sheet 1 to the other sheets is done I
added this code to reProtect each sheet so that noone could manipulate the
data. -->

Sheets("Sheet1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Sheet2").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Sheet3").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("Sheet4").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

The problem I have is since Sheet 1 cell B5-B8 and B11-B17 are not locked
when the macro transfers the data in these cells to cells in Sheet 2,3, and 4
those cells that were locked are not anymore.

How do I set my macro so that when it takes data from Sheet1 Cell B5-B8 and
B11-B17 that it does not unlock the cells the data is transfered to?

Thank you for your help.
Ryan Hess

Hi ryanmhess

Try to use Value instead of copy
You don't have to select the sheets

Sheets("Sheet2").Range("D7").Value = Sheets("Sheet1").Range("B5").Value

--
Regards Ron de Bruin
http://www.rondebruin.nl


"ryanmhess" <[email protected]> wrote in message news:[email protected]...
>I am not fluent in visual basic. For the most part I record operations and
> then cut/paste them to my main macro.
>
> Right now I am working with a workbook with 4 sheets.
>
> Sheet1 - All locked except cells B5-B8 and B11-B17
> - Protected
>
> Sheet2 - All locked
> - Protected
>
> Sheet3 - All locked
> - Protected
>
> Sheet4 - All locked
> - Protected
>
> When I engage the macro the first operation is -->
>
> Sheets("Sheet1").Select
> ActiveSheet.Unprotect
> Sheets("Sheet2").Select
> ActiveSheet.Unprotect
> Sheets("Sheet3").Select
> ActiveSheet.Unprotect
> Sheets("Sheet4").Select
> ActiveSheet.Unprotect
>
> This works.
>
> The next operation in the macro is to take values on sheet 1 and transfer
> them to the other sheets. Here is an example of the data stored on sheet 1
> in cell B5 -->
>
>
> Sheets("Sheet1").Select
> Range("B5").Select
> Selection.Copy
> Sheets("Sheet2").Select
> Range("D7").Select
> ActiveSheet.Paste
> Sheets("Sheet3").Select
> Range("B13").Select
> ActiveSheet.Paste
>
> After all the transfering of data from Sheet 1 to the other sheets is done I
> added this code to reProtect each sheet so that noone could manipulate the
> data. -->
>
> Sheets("Sheet1").Select
> ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
> Sheets("Sheet2").Select
> ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
> Sheets("Sheet3").Select
> ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
> Sheets("Sheet4").Select
> ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
>
> The problem I have is since Sheet 1 cell B5-B8 and B11-B17 are not locked
> when the macro transfers the data in these cells to cells in Sheet 2,3, and 4
> those cells that were locked are not anymore.
>
> How do I set my macro so that when it takes data from Sheet1 Cell B5-B8 and
> B11-B17 that it does not unlock the cells the data is transfered to?
>
> Thank you for your help.
> Ryan Hess

Thank you very much Ron, that worked out nicely and it helped my macro run a
little faster!

Thank you,
Ryan Hess

"Ron de Bruin" wrote:

> Hi ryanmhess
>
> Try to use Value instead of copy
> You don't have to select the sheets
>
> Sheets("Sheet2").Range("D7").Value = Sheets("Sheet1").Range("B5").Value
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "ryanmhess" <[email protected]> wrote in message news:[email protected]...
> >I am not fluent in visual basic. For the most part I record operations and
> > then cut/paste them to my main macro.
> >
> > Right now I am working with a workbook with 4 sheets.
> >
> > Sheet1 - All locked except cells B5-B8 and B11-B17
> > - Protected
> >
> > Sheet2 - All locked
> > - Protected
> >
> > Sheet3 - All locked
> > - Protected
> >
> > Sheet4 - All locked
> > - Protected
> >
> > When I engage the macro the first operation is -->
> >
> > Sheets("Sheet1").Select
> > ActiveSheet.Unprotect
> > Sheets("Sheet2").Select
> > ActiveSheet.Unprotect
> > Sheets("Sheet3").Select
> > ActiveSheet.Unprotect
> > Sheets("Sheet4").Select
> > ActiveSheet.Unprotect
> >
> > This works.
> >
> > The next operation in the macro is to take values on sheet 1 and transfer
> > them to the other sheets. Here is an example of the data stored on sheet 1
> > in cell B5 -->
> >
> >
> > Sheets("Sheet1").Select
> > Range("B5").Select
> > Selection.Copy
> > Sheets("Sheet2").Select
> > Range("D7").Select
> > ActiveSheet.Paste
> > Sheets("Sheet3").Select
> > Range("B13").Select
> > ActiveSheet.Paste
> >
> > After all the transfering of data from Sheet 1 to the other sheets is done I
> > added this code to reProtect each sheet so that noone could manipulate the
> > data. -->
> >
> > Sheets("Sheet1").Select
> > ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
> > Sheets("Sheet2").Select
> > ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
> > Sheets("Sheet3").Select
> > ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
> > Sheets("Sheet4").Select
> > ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
> >
> > The problem I have is since Sheet 1 cell B5-B8 and B11-B17 are not locked
> > when the macro transfers the data in these cells to cells in Sheet 2,3, and 4
> > those cells that were locked are not anymore.
> >
> > How do I set my macro so that when it takes data from Sheet1 Cell B5-B8 and
> > B11-B17 that it does not unlock the cells the data is transfered to?
> >
> > Thank you for your help.
> > Ryan Hess
>
>
>