Clear format & everything from worksheet, except a range of cells' values (VBA) & back

[RobotMan]

Hi, I was wondering if there is a way to toggle clear/hide everything from a worksheet except from a range of cells I could specify and also bring everything back using VBA. The idea is that I want to print a, empty table and later on, print the values over that same paper once I'm ready to do so, then restore the original format once again (using a macro button to toggle it on/off). The range of cells I want to keep their values is C9:S33, everything else: format, conditional formating, objects, text, borders would be cleared/hid, and, with the same button with macro bring back everything once I need it back again.

Hope I could explain myself.

Thank you very much
Screenshot_21.jpg

[Greg M]

Hi there,

See if the following code gets you moving in the right direction - it works by copying a range of values between worksheets, one of which is formatted and the other of which is not:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[RobotMan]

Hi Greg, thank you for your code, unfortunately I don't know what am i doing wrong because when applied, the code do not change anything or there is a run-time error. In fact I don't know what to do, I introduced the code as a module, then created 2 buttons (1 for ClearFormatting and 1 for ShowFormatting) and assigned the macro to each. But when I press any button then the result is negative. What is it that I am missing? Should I do something else? I see in at the beginning of your code:

Please Login or Register  to view this content.

And I do not understand about the Sheet 1 and Sheet 2, do I have to create something else? Can you explain this to me, please?

Thank you again and sorry for my unexperience in VBA

Mike

[Greg M]

Hi again,

Can you post a sample workbook with the worksheet shown in your initial post?

If you can do this I will add the "Without Formatting" worksheet and the necessary code.

Please Login or Register  to view this content.

This value should be the name of a new worksheet without formatting.

This value should be the name of your existing worksheet with formatting.


Regards,

Greg M

[RobotMan]

Hi Greg, with your explanation about the sheets I made it work, but now I face a new problem: My file contains 6 worksheets and I would like to know if you could modify your code so this would work with all six pages, since your code only defines 1 SHEET__WITH_FORMATTING. I'm sending you my file (hope I did it right). Thank you very much!

[Greg M]

Hi again,

See if the attached version of your workbook does what you need.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[RobotMan]

Hi Greg, It works wonderful, thank you for your big help. Appreciate it.

[Greg M]

Hi again,

Many thanks for your feedback.

You're welcome - glad I was able to help.

Regards,

Greg M


P.S. Many thanks also for the Reputation increase - much appreciated!