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.

facebooklinkedin