Insert Copied Rows in protected Sheet. Help

[HiSoC8Y]

Hi everyone.

I have a worksheet that is protected, and i have given the user to insert rows. The user can insert rows, that works. However, if i copy a complete row and try to insert it, it doesnt allow me. I need to do so, because i have formulas in that row which i want it to be copied also.


So what can the solution be?

Thanks, and this is a great place for excel knowledge.

 

[Nimrod]

Hello HiSoC8Y
If you use a VBA process to copy the cells then you could also have the same code unlock and relock the sheet again eg unlock sheet ... copy data ... lock sheet ....

However you approach this you will need to unprotect the sheet to do it.

 

[erik.van.geit]

Hi, HiSoC8Y
Welcome to the Board !!!

You can insert rows, but the cells are protected, so you cannot input formulas or values.
another solution will be needed

a macro could help: what would you like to do ?

kind regards,
Erik

EDIT: Hey! Nimrod!

 

[HiSoC8Y]

Thanks guys for the quick reply.

Im new to vba with excel, i havent done any vba coding, so you guys need to help me on that.

I think a solution could be:

1) a button when i click it, it will enter a complete row with same formulas as the previous row.

2) Everytime i press [enter], a row will be automatically inserted with the formulas and all.

is that possible?

thanks

 

[Smitty]

Welcome to the Board!

Is the row to be copied/inserted static or dynamic, and if the latter what's the determining factor? I.E. Does the user select it and how do you know they'll select a row with the formulas? If it's static, what's the address?

What are the formulas? Some won't require copying: if you insert a range within the existing formula range the range will automatically update in the formula.

Smitty

(Mornin' fellas!)

 

[HiSoC8Y]

The URL is the sample worksheet. between the rows 7-29, i want to insert the similar rows.

Thanks

http://www.megaupload.com/?d=18IBBH33

 

[erik.van.geit]

HiSoC8Y,

your spreadsheet contains formulas which must be copied down (or up )
question of Pennysaver still remains:

Does the user select it and how do you know they'll select a row with the formulas?

basically:
the user will select a row (clicking one cell would be good also)
above (or below) the selection one row will be inserted and will get the formulas of the preceeding (following) row

is this correct ?

kind regards,
Erik

 

[HiSoC8Y]

yes thats how i want it to be..

so how to do it ?

 

[erik.van.geit]

this is the basic macro to start

Sub test()
ActiveSheet.Unprotect "password"
With ActiveCell
.EntireRow.Copy
.Insert Shift:=xlDown
End With
Application.CutCopyMode = False
ActiveSheet.Protect "password"
End Sub

you will probably have some requirements ...

first do some tests please

 

[ChiragAmin]

Erik,
I am new to VBA coding so I just simply copy and paste above code and it works. But it works only when you run the macro.
Is it possible that the same code auto-runs when you click on Paste.
Please advise. Thank you.