Formatted Work Sheet

[jays35]

I have some work sheets, which I maintain, that a couple of my colleagues also use and they copy some of their data into my worksheets. My work sheets are formatted and have some formulas in them and when they copy data into them, they use the paste function, not the paste values functions. How can I protect my formatting a formulas when thy copy their data into them. I have told them to past 'values only', but they use the 'Cntrl V' key as the 'EASY' button.

Thanks

Dave

 

[Kamolga]

Then unprotect the cells they can use and protect the sheet,
Otherwise if it is a square of values with a few formulae here and there, forcing to copy-pace multiple ranges to go through protection, you can set a "paste sheet" where they paste in A1 and you get your info from there in your sheets

 

[jays35]

I want them to match the formats that are already in my worksheets. Unprotectimg them won’t it allow them paste and anything like say their ‘font’?

 

[Kamolga]

No, and basically I thought about a conditional formatting (if z9999=0, bold and yellow font let's say) but if you paste a cell without conditional format into a conditional format, it disappears...also need to use paste values.
To me there is 3 options:

1. Paste sheet/area. If they can't paste properly, they paste on a rubbish sheet and I keep mySheet clean with reference to those cells.
2. They paste on mySheet and I have a macro that copy the format from a hidden mastersheet and paste it over their paste (that works for formatting but not formulae, so need to protect the formulae cells).
3. They can do what they want, after pasting, the macro will put formulae and right format everywhere it needs to, so no protected sheet.

Issues with option 2 and 3 is that it launches a macro whenever a value change (so pasting). So it can slower a file which is middle size (bellow 30mb no problem) and more annoying is that the undo is not working.

If you want to go for option 2 or 3, right click mySheet (or whatever name your sheet has), click on "view code" and paste

[LEFT][COLOR=#101094][FONT=Consolas]Private[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Worksheet_Change[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#101094][FONT=Consolas]ByVal[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Target [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]As[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Range[/FONT][/COLOR][COLOR=#303336][FONT=Consolas])[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
'Change the area ("A1:D100") to where the paste could happen
[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]If[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Not[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Intersect[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Target[/FONT][/COLOR][COLOR=#303336][FONT=Consolas],[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Range[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas]"A1:D100"[/FONT][/COLOR][COLOR=#303336][FONT=Consolas])[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Is[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] [/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas]Nothing[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Then[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
    [/FONT][/COLOR][COLOR=#858C93][FONT=Consolas]'Record a macro where you put the format and formulae or copy-paste from master sheet and call it here[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
    [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]If[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][/LEFT]

 

[jays35]

Thanks and I will give it a try.

 

[Kamolga]

In File/option/customize ribbon you can tick the Developper tab on the right to have access to macro's. Otherwise, to record, there is button down left the screen, next to ready.