VBA :How to save multiple sheet into another excel file and removing formula upon saving

[JOHATUP]

I have this excel sheet: Data, Pivot, Posted sheets that I need to save in one file excluding other sheet. I was able to save the file however the formula still there upon saving, I need the data of the original file to be intact so that what will be save is the correct value/data.?

 

[mumps]

This macro will create the new file with values only.

Sub SaveSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Sheets(Array("Data", "Pivot", "Posted")).Copy
    For Each ws In Sheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
    Application.ScreenUpdating = False
End Sub

 

[JOHATUP]

This macro will create the new file with values only.

Sub SaveSheets()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Sheets(Array("Data", "Pivot", "Posted")).Copy
    For Each ws In Sheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
    Application.ScreenUpdating = False
End Sub

It woks saving the selected sheets. but the formula still there because the data is linked/vlookup sheet that is not included in the saved excel file.

 

[mumps]

This line of code replaces all the formulae in each sheet with the cell value.

ws.UsedRange.Value = ws.UsedRange.Value

I tested the macro on some dummy data with formulae and it worked properly. It would be easier to help if you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[JOHATUP]

This line of code replaces all the formulae in each sheet with the cell value.

ws.UsedRange.Value = ws.UsedRange.Value

I tested the macro on some dummy data with formulae and it worked properly. It would be easier to help if you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Im still uploading the file. thank you!

 

[JOHATUP]

This line of code replaces all the formulae in each sheet with the cell value.

ws.UsedRange.Value = ws.UsedRange.Value

I tested the macro on some dummy data with formulae and it worked properly. It would be easier to help if you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

Dropbox

 

[mumps]

The file you posted has two extensions (xlsx and xlsm) in the name so Excel doesn't recognize it. Please re-save the file with one extension only and re-post.

 

[JOHATUP]

The file you posted has two extensions (xlsx and xlsm) in the name so Excel doesn't recognize it. Please re-save the file with one extension only and re-post.

I see, you are correct idid save it incorrectly as xlsx first but i thought i did change it to xlsm. thank you for noticing. ill check on this and get back to you.

 

[Fluff]

It's an xlsm file. Just remove the .xlsx from the end & it will open.

 

[JOHATUP]

It's an xlsm file. Just remove the .xlsx from the end & it will open.

heres the updated link

Dropbox

www.dropbox.com