Save workbook as new one, changing formulas into values is different ranges.

[MMasiarek]

Hello,

I have the workbook with macros. Workbook structure is: Sheet1 where calculations are made and Sheet2, which is static and is used for keeping variables used for calculations in Sheet1.
In Sheet1 I'm using customized functions/formulas in few different ranges.
I made a button which runs 'copy1' Sub. That sub is saving whole workbook in specified folder and with specified name.

copy1 code:

Sub copy1()

    Dim Paste_path As String
    Dim s As String

    Paste_path = ThisWorkbook.Worksheets("Sheet2").Range("B3").Value

    s = Paste_path & "CHECKLIST_" & ThisWorkbook.Worksheets("Sheet1").Range("E5").Value & ".xlsm"
    ActiveWorkbook.SaveCopyAs FileName:=s

End Sub

What I'm trying to get is:
1). Copy whole Sheet2 into new workbook as it is.
2). From Sheet1 copy ranges A1:E22 and A26:C27 as values into new workbook.
3). From Sheet1 copy ranges A23:E24 and A28:C28 with functions/formulas.
4). Do that when button is clicked.

Could anyone can assist me with this one?

 

[sijpie]

You are doing it the wrong way round? If you (as in your code) save the workbook as, then the only thing you need to do next is set the formulas of the two ranges to values.

Sub MakeSafeCopy()

    Dim Paste_path As String
    

    Paste_path = ThisWorkbook.Worksheets("Sheet2").Range("B3").Value

    Paste_path= Paste_path & "CHECKLIST_" & ThisWorkbook.Worksheets("Sheet1").Range("E5").Value & ".xlsm"

    ActiveWorkbook.SaveCopyAs FileName:=Paste_path
    
    'Now set two ranges to their values
    Range("A1:E22").value=Range("A1:E22").value
    Range("A26:C27").value=Range("A26:C27").value

   activeworkbook.save
End Sub

 

[MMasiarek]

Everything is working now. Thank you for help

 

[MMasiarek]

You are doing it the wrong way round? If you (as in your code) save the workbook as, then the only thing you need to do next is set the formulas of the two ranges to values.

Sub MakeSafeCopy()

    Dim Paste_path As String
   

    Paste_path = ThisWorkbook.Worksheets("Sheet2").Range("B3").Value

    Paste_path= Paste_path & "CHECKLIST_" & ThisWorkbook.Worksheets("Sheet1").Range("E5").Value & ".xlsm"

    ActiveWorkbook.SaveCopyAs FileName:=Paste_path
   
    'Now set two ranges to their values
    Range("A1:E22").value=Range("A1:E22").value
    Range("A26:C27").value=Range("A26:C27").value

   activeworkbook.save
End Sub

I have one more question.
When I'm using this sub, workbook copy is save with values instead of formulas whis is what I wanted.
But formulas in my original excel are also overwritten as values.
Do you know how can I prevent it?
What I want at the end of the process is: original excel with formulas and copy with values instead of some formulas.

 

[sijpie]

Move the line
active workbook.saveas
To the first line of the macro.
I think autosave was messing it up.

 

[MMasiarek]

Move the line
active workbook.saveas
To the first line of the macro.
I think autosave was messing it up.

I think autosave was messing it up.

I tried, same issue.
If I close original excel without saving and open it again all formulas are ok.

What I understand, now it works lik this:

1. Specific ranges are set as values in original excel
2. Original excel it is saved as a copy excel(with values instead of formulas)
3. Original excel stays with values from step 1, until I close it without saving

 

[sijpie]

It should work. For completeness sake I have added an extra save. See the comments.

You can set a breakpoint in VBA at the start of this code (click in the border left of the line where you want the code to pause, the line should turn red). Then use F8 key to step through the code so you can check each operation.

Sub MakeSafeCopy()

    Dim Paste_path As String
    
    ' Save the original file unchanged:
    ActiveWorkbook.Save

    ' Create the new filename for the copy
    Paste_path = ThisWorkbook.Worksheets("Sheet2").Range("B3").Value

    Paste_path= Paste_path & "CHECKLIST_" & ThisWorkbook.Worksheets("Sheet1").Range("E5").Value & ".xlsm"

    ' Save the workbook (with formulas)  as the new name
    ActiveWorkbook.SaveCopyAs FileName:=Paste_path
    
    'only Now set two ranges to their values
    Range("A1:E22").value=Range("A1:E22").value
    Range("A26:C27").value=Range("A26:C27").value

    ' Then save the copy again, this time without the formulae
   activeworkbook.save
End Sub

 

[MMasiarek]

I used code you posted and issue still occurs.
What I'm doing and geting is:

1. I'm opening my original excel.
2. I'm running code.
3. Formulas in ranges A1:E22 and A26:C27 in my opened excel changed into values.
4. Copy of excel is saved but with formulas in ranges A1:E22 and A26:C27.

So it seems to work in opposite way it should. Opened excel should stay with formulas and its copy should have values

 

[sijpie]

Ah, now I see what is happening: You are using the SaveCopyAs method instead of the SaveAs method.

So it saves a copy of the original, then continues with the macro on the current (original) workbook.

If you use the SaveAs method, then the current workbook is the copy.

So:

Sub MakeSafeCopy()

    Dim Paste_path As String
    
    ' Save the original file unchanged:
    ActiveWorkbook.Save

    ' Create the new filename for the copy
    Paste_path = ThisWorkbook.Worksheets("Sheet2").Range("B3").Value

    Paste_path= Paste_path & "CHECKLIST_" & ThisWorkbook.Worksheets("Sheet1").Range("E5").Value & ".xlsm"

    ' Save the workbook (with formulas)  as the new name
    ActiveWorkbook.SaveAs FileName:=Paste_path
    
    'only Now set two ranges to their values
    Range("A1:E22").value=Range("A1:E22").value
    Range("A26:C27").value=Range("A26:C27").value

    ' Then save the copy again, this time without the formulae
   activeworkbook.save
End Sub