
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.