"Locking in" formatting

I want to "lock in" column formats (i.e., custom formats, general, date, number, text) on a worksheet template so that data copied from another source and pasted into a cell does not change the format I locked in. I figured this would be an option in settings but I cannot find such, so I thought perhaps embedding a macro into the template would be able to accomplish this "locking in" of formats so that when a user opens the template and starts pasting in data, it maintains the locked in formatting of the template. The user then saves a non-template version of the worksheet with the data they entered. All formatting locked in even in the saved non-template version.

I follow your logic, but this would require me running a "maintenance" macro after the user does data entry. Your suggestion would streamline the process of doing that for sure. Maybe there's a way to embed the macro in the template so that when the user performs their data entry and hits the save button, it automatically runs the "format painter" maintenance macro before actually saving the document.

Please Login or Register  to view this content.

It's harder than it looks like it should be!

Something occurred to me while working in the garage over the weekend. What if, at the beginning of the macro, I find the last blank row and filldown the entire sheet purple. Then when it is time to filter for blanks I first filter out the color purple. This should leave only "real" blanks that I'm looking for. I'll try this later today and see if it works out.

Anyone else have thoughts on this at all?

ok, changing to this makes the filtering of the color purple work .. so now i'm going to try to delete all the purple, then remove filter and hopefully be left with just my data set and no blank rows.

Please Login or Register  to view this content.