Saving an excel file with multiple sheets as values using a cell reference as the file name

Peter Davison

Active Member
Joined
Jun 4, 2020
Messages
433
Office Version
  1. 365
Platform
  1. Windows
Using VBA code, can you advise how to save a workbook with multiple sheets removing all formulas and save as values retaining all formatting, but using a name entered in "Cell I2" as the file name?

Thanks for your help
 
Thank you for coming back to me.
As I am a beginner in VBA, does the above just go in where I had the old code (with it being a sub and End Sub, or does the other code need to do something to call and end it.
Sorry if I'm talking a bit nonsense stuff I'm just not sure.

Thanks again for all your help.
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
VBA Code:
Sub RunIt()
Dim answer1 As Integer
answer1 = MsgBox("Please Note: This process will remove the user selection options click YES to continue or NO to stop procedure", [B]vbYesNo[/B])
If answer1 = [B]vbYes[/B] Then
       MsgBox "You choose OK. Go replacing."
       ' agreed so run replacing
       call OverwriteFormulaWithValuesAndSave
Else
       MsgBox "You choose NO. Procedure has stop."
       ' not agreed so stop actioning
       Exit Sub
End If
End Sub
 
Upvote 0
Thank you for all your help I'm going to give it a trial run in the morning.
You've been most helpful.
 
Upvote 0
Hi,
I tried the code above and at first it did not work and fell over on the line below (showed red)

answer1 = MsgBox("Please Note: This process will remove the user selection options click YES to continue or NO to stop procedure", vbYesNo)
If answer1 = vbYes Then

I removed the bold highlighted bits and it all ran okay?
What were the and for?

Thank you again for all your help it has been invaluable.
 
Upvote 0
VBA Code:
Sub RunIt()
Dim answer1 As Integer
answer1 = MsgBox("Please Note: This process will remove the user selection options click YES to continue or NO to stop procedure", vbYesNo)
If answer1 = vbYes Then
       MsgBox "You choose OK. Go replacing."
       ' agreed so run replacing
       call OverwriteFormulaWithValuesAndSave
Else
       MsgBox "You choose NO. Procedure has stop."
       ' not agreed so stop actioning
       Exit Sub
End If
End Sub

There should not be any bold tags around vbYesNo and vbYes in code, I've missed that when I was pasting answer.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top