Macro to copy values, formats,hidden formulas and validation to another sheet - Help!

Hi,

I need help!!!!

I have created a macro that copies 2 rows of data, from a hidden sheet to another sheet.
The rows I am copying have some locked cells, validation, formulas, formatting and concatenation that I want to keep in the rows.

The macros unprotect the sheets, run the macro, and then protect the sheet again. The protection keeps cells with formulas locked and hidden, but other cells can be edited, etc...

If I don't add protection (in blue below) to the macro, it works great - but as soon as I add protection, then the formulas don't copy over and the concatenation stops working.

Can anyone please help me?

mCRM MASTER TEMPLATE for TEST 12.05.2012.xlsm

Sub Macro9()
'
' Macro9 Macro
'

Sheets("macros").Protect Password:="646537", _
UserInterFaceOnly:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowSorting:=True, AllowFiltering:=True, _
AllowDeletingRows:=True, AllowInsertingRows:=True

Sheets("mCRM Template").Protect Password:="646537", _
UserInterFaceOnly:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowSorting:=True, AllowFiltering:=True, _
AllowDeletingRows:=True, AllowInsertingRows:=True
Sheets("macros").Activate
ActiveCell.Rows("1:2").EntireRow.Select
Selection.Copy
Sheets("mCRM Template").Activate
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(2, 0).Range("A1").Select
End Sub

Please help!!!
Annie Brown

Hello AnnieBrown. You protect the sheets using your password but they remain protected throughout. I don't see any code that unprotects them.

Try:

Please Login or Register  to view this content.

Hi there.

Thanks for your response! I took what you gave me and had to change it up a little - but it works perfectly now!

ActiveSheet.Unprotect Password:="646537"
Sheets("macros").Activate
ActiveSheet.Unprotect Password:="646537"
Rows("2:3").Select
Selection.Copy
Sheets("mCRM Template").Activate
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(2, 0).Range("A1").Select
Application.CutCopyMode = False
ActiveSheet.Protect Password:="646537", _
DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows:= _
True
Sheets("macros").Activate
ActiveSheet.Protect Password:="646537", _
DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingRows:=True, AllowDeletingRows:= _
True
Sheets("mCRM Template").Activate
End Sub

Glad it worked out.