Formatted Work Sheet

jays35

New Member
Joined
Oct 11, 2016
Messages
10
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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
 
Last edited:
Upvote 0
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’?
 
Upvote 0
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
Code:
[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]
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top