| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro that unprotects worksheets, transfers data, then reprotects
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro that unprotects worksheets, transfers data, then reprote
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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data Transfers?? | Excel Discussion (Misc queries) | |||
| Transfers | Excel Worksheet Functions | |||
| Running a macro to protect a workbook on a already protected workbook UNprotects the workbook ?? | Excel Programming | |||
| Automatic transfers | Excel Worksheet Functions | |||
| FTP Transfers with VBA | Excel Programming | |||
Linear Mode
