Forum Discussion

horsteadjim's avatar
horsteadjim
Copper Contributor
Jul 31, 2017
Solved

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

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 s...
Macros & VBA
Need Help
  • Logaraj Sekar's avatar
    Logaraj Sekar
    Aug 02, 2017

    Hi horsteadjim,

     

    Try this.

    ActiveSheet.Name = Range("C7").Value
horsteadjim's avatar
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's avatar
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

Resources