I have created a spreadsheet where the sheet 1 is a form to be filled in for Quality Control. I have created a "Save" button that will use the 'copy and move' function to create a copy of the first sheet at the end of the same workbook. I need all the completed forms to remain within the original workbook. I need to be able to rename the saved sheet from information in a cell on the first sheet.Is anyone able to assist with a code that will save the copied sheet with the name found in a cell on the main or first sheet?

Hi horsteadjim, Try this.ActiveSheet.Name = Range("C7").Value

Macro so save a sheet with info taken from a cell.

6 Replies

Logaraj Sekar

Iron Contributor

Aug 01, 2017

Hi horsteadjim,

Do this for Copying Current Sheet and Paste it to End of Sheet.

Sub Macro1()
a = Sheets.Count
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Sheets(a)
End Sub

For renaming it, you have to specify cell reference which cell value should come as sheet name.

horsteadjim

Copper Contributor

Aug 02, 2017

Thanks for the reply. I have the copy function done already but I need to be able to rename the sheet from a cell within the sheet . This is the macro that copies the sheet.
Sub QCSave()
'
' QCSave Macro
'

'
Sheets("QC Scorecard").Select
Sheets("QC Scorecard").Copy Before:=Sheets(2)
Sheets("QC Scorecard (2)").Select
Sheets("QC Scorecard (2)").Name = "QC Scorecard (2)"
Sheets("QC Scorecard (2)").Select
Sheets("QC Scorecard (2)").Name = "(=C7)"

The last line is to rename the sheet, where I have C7 is the cell I want to copy from but it does not work.

Zack Barresse

Iron Contributor

Aug 31, 2017

When working with objects, I recommend at least a small level of error handling, as well as explicitly referencing the object hierarchy (book|sheet|range).

Sub QCSave()

    ThisWorkbook.Worksheets("QC Scorecard").Copy Before:=ThisWorkbook.Worksheets(2)
    
    On Error Resume Next
    ThisWorkbook.Worksheets(2).Name = Left(ThisWorkbook.Worksheets(2).Range("C7").Value, 31)
    On Error GoTo 0
    
    If Err.Number <> 0 Then
        MsgBox "Please make sure there are no illegal characters in C7.", vbExclamation + vbOKOnly
    End If
    
End Sub

If you wanted to go a little more robust, like to check if the sheet exists first, it's a couple extra additions:

Sub QCSave()

    Dim Sheet As Worksheet
    Dim SheetExists As Boolean
    Dim SheetName As String

    Set Sheet = ThisWorkbook.Worksheets("QC Scorecard")

    On Error Resume Next
    SheetName = Sheet.Range("C7").Value
    SheetExists = CBool(ThisWorkbook.Worksheets(SheetName).Name <> vbNullString)
    On Error GoTo 0

    If SheetExists Then

        MsgBox "The sheet '" & SheetName & "' already exists.", vbExclamation + vbOKOnly
        Exit Sub

    Else

        Sheet.Copy Before:=ThisWorkbook.Worksheets(2)

        On Error Resume Next
        ThisWorkbook.Worksheets(2).Name = Left(ThisWorkbook.Worksheets(2).Range("C7").Value, 31)
        On Error GoTo 0

        If Err.Number <> 0 Then
            MsgBox "Please make sure there are no illegal characters in C7.", vbExclamation + vbOKOnly
        End If

    End If

End Sub

HTH

Logaraj Sekar
Iron Contributor
Aug 01, 2017
Hi horsteadjim

Use this one in Command Button
Sub Macro1() a = Sheets.Count ActiveSheet.Copy After:=Sheets(a) End Sub

Forum Discussion

Macro so save a sheet with info taken from a cell.

6 Replies

Resources