How to duplicate a sheet without copying formatting using VBA?

To duplicate a sheet in Excel without copying its formatting using VBA, you need to follow a few steps. The basic approach is to create a new sheet and then copy only the values from the original sheet to the new one. Here’s a simple example of how you might accomplish this:

Sub DuplicateSheetWithoutFormatting()
    Dim wsOriginal As Worksheet
    Dim wsNew As Worksheet
    Dim cell As Range
    Dim originalRange As Range
    Dim newCell As Range

    ' Set the worksheet you want to duplicate
    Set wsOriginal = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name

    ' Add a new worksheet to duplicate the data
    Set wsNew = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsNew.Name = wsOriginal.Name & "_Copy" ' Assign a name to the new sheet

    ' Define the range to be copied (used range)
    Set originalRange = wsOriginal.UsedRange

    ' Loop through each cell in the original range and copy only the values
    For Each cell In originalRange
        Set newCell = wsNew.Cells(cell.Row, cell.Column)
        newCell.Value = cell.Value
    Next cell

End Sub

Open Excel and press `ALT + F11` to open the VBA editor.
Insert a new module by clicking “Insert” > “Module”.
Copy and paste the following VBA code into the module.

Explanation

wsOriginal: Represents the original worksheet you want to duplicate.
wsNew: Represents the newly created worksheet where data will be copied.
This script creates a new sheet and then loops through each cell in the original sheet’s used range, copying only the values into the corresponding cells of the new sheet.
The new sheet is named by appending “_Copy” to the original sheet’s name.

Notes

Make sure to update `”Sheet1″` in the line `Set wsOriginal = ThisWorkbook.Sheets(“Sheet1”)` to match the name of your sheet you want to copy.
This script only copies the values from the original sheet, omitting any formatting, formulas, or other attributes.

Once you have added the VBA code, you can run the `DuplicateSheetWithoutFormatting` macro to duplicate the sheet without copying the formatting.

facebooklinkedin