Button to save range from active sheet as a new sheet

Hi E_Sean_Sullivan,

you can try this as a solution for a button to save a range of cells from the active sheet as a new sheet:

Sub Button3_Click()
    ' Create a Range variable to store the range of cells to copy.
    Dim rng As Range

    ' Select the range of cells to copy.
    Set rng = ActiveSheet.Range("A1:B10")

    ' Copy the range of cells to the new sheet.
    rng.Copy after:=Sheets(Sheets.Count)

    ' Name the new sheet.
    Sheets(Sheets.Count).Name = GetName
End Sub

Private Function GetName() As String
    Dim x As Long, n As String
    n = ActiveSheet.Range("A1")
    If SheetExists(n) Then
        Do
            x = x + 1
            If Not SheetExists(n & x) Then Exit Do
        Loop
        n = n & x
    End If
    GetName = n
End Function

Private Function SheetExists(aName As String) As Boolean
    On Error Resume Next
    Dim sh As Worksheet: Set sh = Sheets(aName)
    If Err = 0 Then SheetExists = True Else SheetExists = False
End Function

To use this code, simply select the range of cells that you want to copy and then click the button. The code will create a new sheet at the end of the workbook and copy the range of cells to the new sheet.

You can also modify the code to copy a range of cells from a different sheet, or to copy multiple ranges of cells to the new sheet. To do this, simply modify the Range() function in the Button3_Click() subprocedure to select the range of cells that you want to copy.