Excel Forum

Code: Select all

Public Sub AddNewSheet()
    Dim newSheetName As String
    On Error Resume Next
        newSheetName = ActiveSheet.Range("J1").Value
        Sheets.Add After:=ActiveSheet
            ActiveSheet.Name = newSheetName
        Sheet12.Range("B3:H50").Copy
            Sheets(newSheetName).Range("A1").PasteSpecial xlPasteValues
            Range("A1").Select
            Sheet12.Select
            Range("B3").Select
        Application.CutCopyMode = False
End Sub

Code: Select all

Public Sub AddNewSheet()
    Dim newSheetName As String
    On Error Resume Next
    newSheetName = ActiveSheet.Range("J1").Value
    If Sheets(newSheetName) Is Nothing Then
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Name = newSheetName
    End If
    Sheet12.Range("B3:H50").Copy
    Sheets(newSheetName).Range("A1").PasteSpecial xlPasteValues
    Range("A1").Select
    Sheet12.Select
    Range("B3").Select
    Application.CutCopyMode = False
End Sub