How to copy a range to a new sheet with a specific name using VBA?

To copy a range to a new sheet with a specific name using VBA, you can follow these steps. Below is a sample VBA code that demonstrates how to perform this action:

Sub CopyRangeToNewSheet()
    Dim wsSource As Worksheet
    Dim wsNew As Worksheet
    Dim rngToCopy As Range
    Dim newSheetName As String

    ' Set the source worksheet
    Set wsSource = ThisWorkbook.Sheets("Sheet1") ' Adjust the source sheet name as needed

    ' Define the range to copy
    Set rngToCopy = wsSource.Range("A1:D10") ' Adjust the range as needed

    ' Set the name for the new sheet
    newSheetName = "CopiedDataSheet" ' Change to the desired name

    ' Check if the sheet already exists, if yes, delete it
    On Error Resume Next
    Set wsNew = ThisWorkbook.Sheets(newSheetName)
    If Not wsNew Is Nothing Then
        Application.DisplayAlerts = False
        wsNew.Delete
        Application.DisplayAlerts = True
    End If
    On Error GoTo 0

    ' Add a new worksheet with the specified name
    Set wsNew = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsNew.Name = newSheetName

    ' Copy the range to the new sheet
    rngToCopy.Copy Destination:=wsNew.Range("A1")

    ' Optional: Just to move the focus back to the new sheet
    wsNew.Select
End Sub

Explanation:

  • wsSource: Represents the worksheet where the original data is located.
  • rngToCopy: Represents the range you want to copy. Change `”A1:D10″` as needed to define the specific range you’re interested in.
  • newSheetName: Is the name you’d like to give to your new sheet. Ensure it doesn’t conflict with existing sheet names, or the code will delete the existing sheet with this name.
  • Error Handling: The code checks if a sheet with the name already exists and deletes it if it does. This prevents an error when trying to add a sheet with a duplicate name.
  • wsNew: Represents the newly created worksheet to which the range is copied.

To use this code, simply insert it into a VBA module within the Excel workbook where you want to perform the operation. Adjust the `wsSource` and `rngToCopy` to point to the correct sheet and range in your workbook.

Unlock Your Potential

Excel

Basic - Advanced

Access

Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project