Macro to erase formulas and Save under same name + Date

**vedranc** · 10-19-2010, 12:21 PM

Hello everyone..

I searched for a macro to suit my needs but didn't find it..

I'm looking for a macro to save current sheet (always the first one, always with same name) but only with values (it has vlookups) and formats into another workbook under same workbook name + date. With option to define where to save, to ask the user where to save.

If you can, please explain a bit how the code works, so I can learn..

Thanks!

Vedran C.

**blane245** · 10-19-2010, 01:04 PM

Here is a macro that may suit your needs. You can place this in a Module in the workbook you are working with. I have included comments to explain the code. Please ask if you need further assistance.

Please Login or Register  to view this content.

**vedranc** · 10-19-2010, 03:21 PM

Thank you!

What does this mean: xlPasteValuesAndNumberFormats, Number formats? I added Paste Special Paste Formats to leave the cell color.

Also, how to change the date order? To be more readable, like 19-10-10?

Another thing. Many cells have vlookups, so when they are copied to another workbook, without formula it shows #N/A. How can that be erased. So if there is no value in the cell, then it's empty.

Edit: I figured the date format..dd-mm-yyyy

**blane245** · 10-19-2010, 03:35 PM

Originally Posted by vedranc

Thank you!

What does this mean: xlPasteValuesAndNumberFormats, Number formats? I added Paste Special Paste Formats to leave the cell color.

Also, how to change the date order? To be more readable, like 19-10-10?

Another thing. Many cells have vlookups, so when they are copied to another workbook, without formula it shows #N/A. How can that be erased. So if there is no value in the cell, then it's empty.

Edit: I figured the date format..dd-mm-yyyy

Below I added the paste format like you wanted, changed, the date format, and replaced all "N/A" with blanks. Does that do ya?

Please Login or Register  to view this content.

**vedranc** · 10-19-2010, 03:43 PM

Excellent!

Once again, thank you.
If I'll need something else, I'll let you know..
And i'll probably ask you some more questions regarding the code when i go through it again, to learn.

BTW, as a beginner in making macro, code writing, what should I read? I don't need any advanced knowledge but something to start with, so I could make some macros myself..

**blane245** · 10-19-2010, 03:56 PM

There are many ways to learn VBA and I don't know what suits you best. There are many tutorials on Microsoft's MSDN web site. Here is one example. You can also use the VBA help to see what an individual collection, object, property, method, or event handler does, either by searching on the name, or put the cursor in the word in the code and pressing F1. Hope this helps a little.