Saving active workbook in a new workbook as values only

[MoKDiab]

Hello
I want to save the active workbook with all its tabs on a new workbook only as values -all the tabs still individually at the new work book- StephenCrump helped me with the following code but it saves all the active workbook's tabs at just one tab on the new work book I want them to be just as they were on the old one but as values only
Thanks

VBA Code:
Sub SaveValues()

Dim SourceBook As Workbook, DestBook As Workbook, SourceSheet As Worksheet, DestSheet As Worksheet
Dim SavePath As String
Dim LastRow As Long, i As Long, count As Long

Application.ScreenUpdating = False

Set SourceBook = ThisWorkbook

'*********************************************
'Edit next two lines as necessary
SavePath = Sheets("Sheet1").Range("F7").Text
'*********************************************

Application.DefaultSheetDirection = xlRTL 'if necessary
Set DestBook = Workbooks.Add

Application.DisplayAlerts = False
For i = DestBook.Worksheets.count To 2 Step -1
DestBook.Worksheets(i).Delete
Next i
Set DestSheet = DestBook.Worksheets(1)
Application.DisplayAlerts = True

count = 1
For Each SourceSheet In SourceBook.Worksheets
With SourceSheet
LastRow = .Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
.Range("1:" & LastRow).Copy
With DestSheet.Range("A" & count)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
count = count + LastRow
End With
Next SourceSheet

DestSheet.Name = "Some name"
DestBook.Activate
With ActiveWindow
.DisplayGridlines = False
.DisplayWorkbookTabs = False
End With
SourceBook.Activate

Application.DisplayAlerts = False 'Delete if you want overwrite warning
DestBook.SaveAs Filename:=SavePath
Application.DisplayAlerts = True 'Delete if you delete other line

SavePath = DestBook.FullName
DestBook.Close 'Delete if you want to leave copy open
MsgBox ("A copy has been saved to " & SavePath)
Application.DefaultSheetDirection = xlLTR 'if necessary

End Sub

[/CODE]

 

[SQUIDD]

Hi

As an example, the below saves a copy of the workbook as values only.

However, it leaves the new created workbook open.
If this is on the correct lines, then perhaps tell us if you want your original workbook to remain open and close the newly saved?

obviously you need to change the name and directoty of the save.

Sub save_values()
ActiveWorkbook.SaveAs Filename:="C:\Users\daveb\Desktop\new name.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.UsedRange.Value = ActiveSheet.UsedRange.Value
Next ws
ActiveWorkbook.Save
End Sub

 

[Fluff]

@SQUIDD That is going to put the values of the active sheet onto every sheet.

 

[SQUIDD]

fluff

so it is lol, good spot. this should be better

       Sub save_values()
ActiveWorkbook.SaveAs Filename:="C:\Users\daveb\Desktop\new name.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.UsedRange.Value =ws.UsedRange.Value
Next ws
ActiveWorkbook.Save
End Sub

 

[MoKDiab]

Hello
Thanks for replying I want it to take the path and name from the cell I choose like the code at the main post and want it to copy and past as values because there is some sheets change the cells from general to custom by itself when I save the sheet as
Thanks