Copy all sheets to new workbook without formulas and macros

Remco678

New Member
Joined
Jun 1, 2022
Messages
2
Office Version
  1. 2021
Platform
  1. Windows
Hi All,

I want to copy my whole workbook (xlsm) with formulas and macros to a whole new excel file (xlsx) without any formula and macro. I've found the script below on internet and works fine but is for 1 certain sheet. How can i include multiple sheets and copy this to the new workbook? It is important that the original file keeps the formulas and macros.

Thanks in advance

VBA Code:
Sub Tweedeopslaan()
    Dim SourceBook As Workbook, DestBook As Workbook, SourceSheet As Worksheet, DestSheet As Worksheet
    
    Dim SavePath As String, i As Integer
    
    Set SourceBook = ThisWorkbook
    
    '*********************************************
    'Edit next two lines as necessary
    SavePath = "C:\Users\User\Desktop\New folder\TestSaveValues.xlsx"
    Set SourceSheet = SourceBook.Sheets("Juni")
    '*********************************************
    
    Set DestBook = Workbooks.Add
    Set DestSheet = DestBook.Worksheets.Add
    
    Application.DisplayAlerts = False
    For i = DestBook.Worksheets.Count To 2 Step -1
        DestBook.Worksheets(i).Delete
    Next i
    Application.DisplayAlerts = True
    
    SourceSheet.Cells.Copy
    With DestSheet.Range("A1")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats 'Delete if you don't want formats copied
    End With
    
    DestSheet.Name = SourceSheet.Name
    
    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)
End Sub
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Personally I would just cycle through the current workbook and set each sheet to values, then saveas an xlsx which would remove the VBA.

Option Explicit

VBA Code:
Sub SaveFlatCopy()
Dim SavePath As String
Dim sh As Worksheet
Dim reopenWB as String
Dim calcMode As XlCalculation

SavePath = ThisWorkbook.Path & "\"
reopenWB = Thisworkbook.FullName

With Application
    calcMode = .Calculation
    .DisplayAlerts = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
    
'save copy workbook as backup, just in case!
ThisWorkbook.SaveCopyAs SavePath & "Master_backup.xlsm"
ThisWorkbook.Save ' save just because belt n braces

'loop through sheets and set the used range to values
For Each sh In ThisWorkbook.Worksheets
    With sh.UsedRange
        .Value = .Value
    End With
Next sh

'save as xlsx to remove macros
ThisWorkbook.SaveAs SavePath & "FlatVersion" & Format(Now, "yyyymmddhhmmss"), xlOpenXMLWorkbook
'reopen the original file
Workbooks.Open (reopenWB)
'close the flat file
ThisWorkbook.Close False

With Application
    .Calculation = calcMode
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

End Sub
 
Upvote 0
Thanks for the feedback. Could you mark the post as solved. Thank you :)
 
Upvote 0

Forum statistics

Threads
1,223,948
Messages
6,175,579
Members
452,652
Latest member
eduedu

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