How to duplicate a sheet without formulas using VBA?

If you want to duplicate a sheet in Excel without copying its formulas, you can achieve this by using VBA to copy the values alone. Here’s a step-by-step guide on how to do this:

Sub CopySheetWithoutFormulas()
    Dim originalSheet As Worksheet
    Dim newSheet As Worksheet
    Dim cell As Range
    
    ' Set the original sheet that you want to copy
    Set originalSheet = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name
    
    ' Add a new sheet to the workbook
    Set newSheet = ThisWorkbook.Sheets.Add(After:=originalSheet)
    newSheet.Name = originalSheet.Name & "_Copy" ' You can change the naming convention as needed
    
    ' Copy data from original sheet to new sheet without formulas
    For Each cell In originalSheet.UsedRange
        ' Copy only the values
        newSheet.Cells(cell.Row, cell.Column).Value = cell.Value
    Next cell
    
    MsgBox "Sheet copied without formulas.", vbInformation
End Sub

Open Excel and Press `ALT + F11`: This will open the Visual Basic for Applications (VBA) editor.
Insert a New Module:

In the VBA editor, click on `Insert` in the menu bar.
Select `Module` from the dropdown. This will insert a new module where you can write your code.

Write the VBA Code:

Copy and paste the following VBA code into the module window:

Adjust the Code:

Make sure to replace `”Sheet1″` with the name of the sheet you want to duplicate.
Optionally, you can modify the naming convention of the new sheet according to your preference.

Run the VBA Macro:

You can run the macro by pressing `F5` while in the VBA editor, or by going back to Excel and running the macro from the `Developer` tab under `Macros`.

This script will create a new worksheet and copy only the values from the original sheet, leaving out any formulas. The new sheet will be placed immediately after the original sheet and can be named as per your requirements.

facebooklinkedin