Simple VBA Code help

[Rob_010101]

Hello,

My excel is a master document that pulls in data from different excel sheets in different departments, each of which are all updated daily. I need to save the "master" excel once a week into a different excel with all the values pasted. This creates a weekly snapshot of the data as it is at the point of saving but leaves the "master" excel with all the formulas, so it keeps updating.

I have tried to use the below assigned to a button however in addition to creating a new document with paste values, it is also pasting values in the "master" document rendering it useless.

Sub MakeBook()
Dim sh As Worksheet
For Each wb In Sheets
    wb.Activate
    Cells.Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues
Next
With ActiveWorkbook
    s = Replace(.FullName, .Name, Format(Date, "dd-mmm-yy")) & ".xlsm"
End With
ActiveWorkbook.SaveAs Filename:=s
End Sub

It should "Save As" and save into a new excel, as the date, with values pasted. The "master" excel i.e. the sheet being saved from should retain all the formulas.

Hope that makes sense.

Chris

 

[Rob_010101]

I think the problem has to do with .SaveCopyAs s ,since for whatever reason, MS chose not to modify that function to support SharePoint & OneDrive http style paths. See if this works for you.

Sub MakeBook()
    Dim sh As Worksheet, wb As Workbook, s As String, ABFilePath As String
    Set wb = ActiveWorkbook
   
    ABFilePath = wb.FullName
    s = Replace(wb.FullName, wb.Name, Format(Date, "dd-mmm-yy")) & ".xlsm"
   
    For Each sh In wb.Worksheets
        sh.UsedRange.Value = sh.UsedRange.Value
    Next sh
   
    wb.SaveAs Filename:=s                       'save to SharePoint filepath
    DoEvents
    MsgBox "New workbook '" & s & "' has been created. ", vbOKOnly, Application.Name
    Workbooks.Open Filename:=ABFilePath         're-open original workbook
    wb.Close Savechanges:=False 'exit
End Sub

Hi, yes it was the ".SaveCopyAs s" line

The new code now works but pastes values in the master template sheet like it was doing originally.

 

[rlv01]

The new code now works but pastes values in the master template sheet like it was doing originally.

Are you sure you have not misinterpreted? I have tested it on my end and it creates a new workbook and does not save any changes to the "master". I have commented the code to explain the steps.

Sub MakeBook()
    Dim sh As Worksheet, wb As Workbook, s As String, ABFilePath As String
    Set wb = ActiveWorkbook
    
    ABFilePath = wb.FullName    'It is assumed that the active Workbook at this point is "SSD Master Template V5.xlsm"
                                '(note that if the code is always going to be located in "SSD Master Template V5.xlsm" it
                                'would be better to use 'ThisWorkbook' instead of 'ActiveWorkbook').
    
    'A. Create new file path and name
    'For purposes of this example, the date is assumed to be 14-Feb-2024
    'so new file name will be "14-Feb-24.xlsm"
    
    s = Replace(wb.FullName, wb.Name, Format(Date, "dd-mmm-yy")) & ".xlsm"
    Debug.Print s
    
    
    'B. Convert worksheet formulas to values. At this point the workbook is still SSD Master Template V5.xlsm
    'but the changes will not be saved to SD Master Template V5.xlsm.
    For Each sh In wb.Worksheets
        sh.UsedRange.Value = sh.UsedRange.Value
    Next sh
    
    'C. This next step will do two things:
    '1. It will use SSD Master Template V5.xlsm to create 14-Feb-24.xlsm via the .SaveAs function.
    '2. It will close SSD Master Template V5.xlsm without saving any of the changes in Step B.
    wb.SaveAs Filename:=s                       'save to SharePoint filepath
    
    DoEvents
    MsgBox "New workbook '" & s & "' has been created. ", vbOKOnly, Application.Name
    
    'D. Re-open SSD Master Template V5.xlsm
    Workbooks.Open Filename:=ABFilePath         're-open original workbook SSD Master Template V5.xlsm (formulas should be intact).
    wb.Close Savechanges:=False                 'close Workbook 14-Feb-24.xlsm
End Sub

 

[Rob_010101]

Are you sure you have not misinterpreted? I have tested it on my end and it creates a new workbook and does not save any changes to the "master". I have commented the code to explain the steps.

Sub MakeBook()
    Dim sh As Worksheet, wb As Workbook, s As String, ABFilePath As String
    Set wb = ActiveWorkbook
   
    ABFilePath = wb.FullName    'It is assumed that the active Workbook at this point is "SSD Master Template V5.xlsm"
                                '(note that if the code is always going to be located in "SSD Master Template V5.xlsm" it
                                'would be better to use 'ThisWorkbook' instead of 'ActiveWorkbook').
   
    'A. Create new file path and name
    'For purposes of this example, the date is assumed to be 14-Feb-2024
    'so new file name will be "14-Feb-24.xlsm"
   
    s = Replace(wb.FullName, wb.Name, Format(Date, "dd-mmm-yy")) & ".xlsm"
    Debug.Print s
   
   
    'B. Convert worksheet formulas to values. At this point the workbook is still SSD Master Template V5.xlsm
    'but the changes will not be saved to SD Master Template V5.xlsm.
    For Each sh In wb.Worksheets
        sh.UsedRange.Value = sh.UsedRange.Value
    Next sh
   
    'C. This next step will do two things:
    '1. It will use SSD Master Template V5.xlsm to create 14-Feb-24.xlsm via the .SaveAs function.
    '2. It will close SSD Master Template V5.xlsm without saving any of the changes in Step B.
    wb.SaveAs Filename:=s                       'save to SharePoint filepath
   
    DoEvents
    MsgBox "New workbook '" & s & "' has been created. ", vbOKOnly, Application.Name
   
    'D. Re-open SSD Master Template V5.xlsm
    Workbooks.Open Filename:=ABFilePath         're-open original workbook SSD Master Template V5.xlsm (formulas should be intact).
    wb.Close Savechanges:=False                 'close Workbook 14-Feb-24.xlsm
End Sub

Auto save strikes again.

I just disabled this and tried the code and it works. I think what it was doing was autosaving step B into the main document.

Thank you, @rlv01. Your help on this is very much appreciated.