Excel vba copy sheet to new sheet

A classic!

Here's a simple example of how to copy a sheet to a new sheet in Excel using VBA:

Sub CopySheetToNewSheet()
    Dim ws As Worksheet
    Dim newWs As Worksheet

    ' Set the sheet you want to copy
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Create a new sheet
    Set newWs = ThisWorkbook.Worksheets.Add

    ' Copy the contents of the original sheet to the new sheet
    ws.UsedRange.Copy Destination:=newWs.Range("A1")

    ' Rename the new sheet
    newWs.Name = "CopiedSheet"
End Sub

Let me explain what's happening:

  1. We set the ws variable to the sheet we want to copy, which is Sheet1 in this example.
  2. We create a new sheet using the Worksheets.Add method.
  3. We copy the contents of the original sheet to the new sheet using the Copy method and specifying the destination range as A1 on the new sheet.
  4. We rename the new sheet to CopiedSheet.

To run this code, follow these steps:

  1. Open the Visual Basic Editor by pressing Alt + F11 or by navigating to Developer > Visual Basic in the ribbon.
  2. In the Editor, click Insert > Module to insert a new module.
  3. Paste the code into the module.
  4. Click Run > Run Sub/User Form to execute the code.

This will create a new sheet with the same contents as Sheet1 and rename it to CopiedSheet.

Note: You can modify the code to suit your specific needs, such as changing the name of the new sheet or copying only specific ranges or formats.