VBA Macro for Replacing Formulae with Only Data for Multiple Files

[Morshed_Dhaka]

Hello Everyone, I am looking for a macro for Copy &Paste Formulas for a multiple files stored in a folder. For Example: I have afolder named “monthly calculations” which is kept in C drive desktop. There are12 excel file inside that folder which every excel file consists many excelformulas. But when I want to share that folder with my colleagues, I don’t wantto share the formula’s which I used for the necessary calculations. So what I wantto do is running a macro so that every formula of that 12 files convert intoonly data mode. There will be no formula available in those 12 files. The macrocan be run from another macro enabled excel file. Looking for anyone’s urgenthelp regarding this issue.
Thanks in advance

 

[Yongle]

This replaces formula in every sheet in every file in the nominated folder and assumes that only Excel files are in the folder

Amend the folder path and run

Sub ReplaceFormula()
    Const fldr = "[COLOR=#b22222]C:\folder\subfolder[/COLOR]"
    Dim nm As String, wb As Workbook, ws As Worksheet
    nm = Dir(fldr & "\")
    Do While Len(nm) > 0
        Set wb = Workbooks.Open(fldr & "\" & nm)
            For Each ws In wb.Worksheets
                With ws.UsedRange
                    .Cells.Copy
                    .Cells.PasteSpecial xlPasteValues
                End With
                Application.CutCopyMode = False
            Next ws
        nm = Dir
        wb.Close True
    Loop
End Sub

 

[Morshed_Dhaka]

Fantastic. But when I run the code from another macro enabled excel file what is happening that it opened all the excel file from the destination folder and then covert those into only data and then close those file. I am looking a code where not a single file will be opened. When I run the code from another excel file, it will simply remove all the formulas from all the excel file of that folder without opening those and covert those into only data. Is there any way to do that? Please help.

 

[Yongle]

You cannot do what you want

Have you considered
1. creating data-only versions when you save the original workbooks (no VBA required in destination workbook)
Or
2. Using sheet protection to hide formula (no VBA required in destination workbook) see here
OR
3. having "data-only" and "formula" sheets in the workbooks and hiding "formula" sheets (requires VBA in destination workbook)
Requires VBA to unhide the sheet if hidden like this

Sheets("Formula").Visible = xlSheetVeryHidden