protected cells

denniswilburn · Mar 29th, 2002, 09:11 AM

I have a large excel sheet that I use at work. I have lots of formulas so I protect the cells from being overwritten. Every month I have to transfer data from clients that use the same excel sheet. Now, here is my question. I want to copy their entire worksheet and paste everything into my worksheet without disrupting my locked cells. I keep getting the message that the worksheet is protected, etc... If I only grab the data (from their worksheet) that is not protected and copy and paste into mine then that would be long hours and tedious because about every 5th row and about every other cell is protected. How can I do this? Please help. Time is running out on a deadline. E-mail me if you would like to help and need to know more information. Thanks. denniswilburn@hotmail.com

smozgur · Mar 29th, 2002, 10:07 AM

Hi Dennis,

There are several ways but this is my favorite.

You should name the cells which you want to transfer by multiply selecting them then use this code:

workbooks("Target.xls").Sheets(1).range("myrange")=workbooks("Data.xls").Sheets(1).range("myrange")

Assume Data is client file, Target is your protected file, myrange is the range of cells in the Sheet1 for both workbook.

This code will do what you need without prompting you anyway. I hope you like it.

regards

_________________
Oz ~ TheWordExpert

[ This Message was edited by: smozgur on 2002-03-29 09:08 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 29th, 2002, 09:11 AM	#1
denniswilburn New Member Join Date: Mar 2002 Posts: 1	I have a large excel sheet that I use at work. I have lots of formulas so I protect the cells from being overwritten. Every month I have to transfer data from clients that use the same excel sheet. Now, here is my question. I want to copy their entire worksheet and paste everything into my worksheet without disrupting my locked cells. I keep getting the message that the worksheet is protected, etc... If I only grab the data (from their worksheet) that is not protected and copy and paste into mine then that would be long hours and tedious because about every 5th row and about every other cell is protected. How can I do this? Please help. Time is running out on a deadline. E-mail me if you would like to help and need to know more information. Thanks. denniswilburn@hotmail.com

Mar 29th, 2002, 10:07 AM	#2
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	Hi Dennis, There are several ways but this is my favorite. You should name the cells which you want to transfer by multiply selecting them then use this code: workbooks("Target.xls").Sheets(1).range("myrange")=workbooks("Data.xls").Sheets(1).range("myrange") Assume Data is client file, Target is your protected file, myrange is the range of cells in the Sheet1 for both workbook. This code will do what you need without prompting you anyway. I hope you like it. regards _________________ Oz ~ TheWordExpert [ This Message was edited by: smozgur on 2002-03-29 09:08 ]