Vba Code to copy the formula file as values and save it another location

[Masa1989]

Hi guys

I have 20 excel workbook basically for different models each having 4 sheets .
All 20 workbook is loads of formulas, i intend to send these workbooks to client as values.

There will lot of back and forth communication once i send and i have alter lot of numbers after i send.

Once all the file is final, is there any code where i can put it in all model workbook to basically save the first three sheet of workbook as values and get copied in one file location retaining the formula file as it is?

So when i have to change the number again , ill change the numbers and run the macro.



Thanks for any assistance

[Bernie Deitrick]

Sure - a simple macro will convert all the formulas to values:
To convert all sheets in the activeworkbook to values:

Please Login or Register  to view this content.

To convert just the first 3 sheets to values

Please Login or Register  to view this content.

The add a SaveAs to either of these

Please Login or Register  to view this content.

[Masa1989]

hi bernie thanks a lot...

i have limited knowledge in vba..how do i combine all three as one code?
and will it retain my original formula file?

Thanks again

[Bernie Deitrick]

You would only use one of the first two, and one line form the last, like this - but this assumes the active workbook is an xlsx file, this macro resides in another file (like your personal.xlsb) and you need to change the folder path.

Please Login or Register  to view this content.

[Masa1989]

hi its not working

is it because of undeclared sh?
and my active workbook is xlsm, wouldnt it work?

[Bernie Deitrick]

You need to have a valid path to save the file... what code are you using?

Please Login or Register  to view this content.

[Masa1989]

Hi bernie

U had used next sh, shouldnt it be next i?

Thanks

[Bernie Deitrick]

D'oh! Sorry - you're correct: I copied and pasted the first one and did a bad job of editing the pasted code.

Sub ConvertToValues5()
Dim i As Integer

For i = 1 To 3
Worksheets(i).UsedRange.Value = Worksheets(i).UsedRange.Value
Next i

ActiveWorkbook.SaveAs "C:\Users\anzafsa\Excel\" & ActiveWorkbook.Name, 52 'xlsm and a valid path

End Sub

[Masa1989]

No worries..
However one small doubt bernie, if i want to modify the [Worksheets(i).UsedRange.Value = Worksheets(i).UsedRange.Value], say i want to exclude column f and g from copy paste as values, i want to retain those 2 columns as formula, rest everything as values, whats the code?

Thanks

[Bernie Deitrick]

Please Login or Register  to view this content.

could become:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

The last one won't work if your data extends to within 6 columns of XFD....

[Masa1989]

Wow awesome bro....genius stroke!!

Thanks a tonne.....

[Masa1989]

Hi.

Please Login or Register  to view this content.

This code doesnt give me the same number formats as the source file, ex:if i have '058 in my source file, if i run the macro it gets converted to 58.
same with '00, '1E7 etc,
can this code be modified to keep the same number formats after i run the macro?

Thanks for any help

[Bernie Deitrick]

It should not change the formatting, but try

Please Login or Register  to view this content.