Saving active workbook in a new workbook as values only

MoKDiab

New Member
Joined
Dec 15, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
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]
 
Last edited:

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,998
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,448
Office Version
  1. 365
Platform
  1. Windows
@SQUIDD That is going to put the values of the active sheet onto every sheet. ;)
 

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
1,998
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
fluff

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

VBA Code:
       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

New Member
Joined
Dec 15, 2021
Messages
10
Office Version
  1. 2019
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,681
Messages
5,838,762
Members
430,567
Latest member
carid

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
Top