How do I write a macro which will create a "Publishable" version of a workbook (formulas removed) and save it to a specified SharePoint location?

Question

Unable to write a macro to automatically create a 'Publishable" version of an excel workbook (so that other people have access but cannot overwrite any formulas or see the lookup worksheets).

I need to create publishable versions of 6 workbooks each month.

All workbooks are saved in a Sharepoint Library, using the same naming convention ("2023 Training Matrix NOps", "2023 Training Matrxi SOps" etc.)

The publishable versions need to be saved in a sub folder in the Sharepoint library.

The code below, is the closest I could find to fit my needs:

Actual Outcome

• Creates a copy of the workbook with values and formatting only.

• Saves file to the same location as the original file.

• Removes formulas from the original workbook.

• Take a long time to run.

Desired Outcome

1. Save a copy of the original workbook (called "2023 Training Matrix NOps") to a sub folder within the original SharePoint folder

2. Name the copy "2023 Training Matrix NOps - Save Date" (e.g., "2023 Training Matrix NOps - 20_06_2023")

3. The copy of the workbook should be:

• macro free (file type xlsx);

• have all formulas converted to values;

• retain all formatting;

• remove the lookup worksheets which are all coloured yellow (worksheet names are "ADMIN", SF_Item_Extract_SF_Item_Status_EC_Report).

It is critical that the original workbook is persevered in its original location, with all formulas and worksheets intact.

Sub mcrSet_All_Values_and_Save_XLSX()

    Dim w As Long
    For w = 1 To Sheets.Count
        Sheets(w).UsedRange = Sheets(w).UsedRange.Value
    Next w

    Application.DisplayAlerts = False

    ThisWorkbook.SaveAs _
      ThisWorkbook.Path & Chr(92) & _
      Left(ThisWorkbook.Name, InStr(1, ThisWorkbook.Name, Chr(46)) - 1) & Format(Date, "dd-mm-yyyy"), _
      xlOpenXMLWorkbook

End Sub

Answer 1

I expect that the conversion/rewriting of ALL the cell contents takes more time than looping through the cells containing a formula in .UsedRange. I haven't done any performance testing but expect this to be quicker

Sub test()
    Dim sht As Worksheet, r As Integer, c As Integer
    For Each sht In Worksheets
        With sht.UsedRange
            For r = 1 To .Rows.Count
                For c = 1 To .Columns.Count
                    If WorksheetFunction.IsFormula(.Cells(r, c)) Then
                       .Cells(r, c).Value = .Cells(r, c).Value
                    End If
                Next
            Next
        End With
    Next
End Sub

To remove the yellow marked tabs, add this at the beginning of the loop:

    Dim bDisplayAlertsBefore As Boolean
    For Each sht In Worksheets
        If sht.Tab.Color = vbYellow Then
           bDisplayAlertsBefore = Application.DisplayAlerts
           Application.DisplayAlerts = False
           sht.Delete
           Application.DisplayAlerts = bDisplayAlertsBefore
        Else
           With ...

Because you are modifying cells and removing reference data in the same loop, you might want to switch off calculation during the procedure. Put this at the beginning:

    Dim iCalculationBefore As Integer
    iCalculationBefore = Application.Calculation
    Application.Calculation = xlCalculationManual

and at the end:

    Application.Calculation = iCalculationBefore