Run Time Error 1004 & blank cell 1

[MJamison07]

I have an excel worksheet that contains two macros (which I developed by combining information from several posts on tek-tips - THANK YOU!)

1. to name the sheet based on the value in cell "c6"
2. copy a hidden base sheet (add'l serv) that is run from a button on worksheet. In the base sheet that copies, c6 is blank.

I hit the button and it copies the base sheet beautifully. But when I select any cell on the new sheet, I get an error: Run time error 1004.

I have very little experience with macros, but I'm assuming it's because "c6" is blank. I do not know how to fix.

Here are my two macros:

Renaming Sheet:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveSheet.Select
ActiveWorkbook.Unprotect ("password")
ActiveSheet.Name = ActiveSheet.Range("c6")
ActiveWorkbook.Protect ("password")
End Sub

Coping Base Sheet:
Sub CopySheet()
ActiveWorkbook.Unprotect ("password")
Sheets("Add'l Serv").Visible = True
Sheets("Add'l Serv").Copy After:=Sheets(Sheets.Count)
Sheets("Add'l Serv").Visible = False
ActiveWorkbook.Protect ("password")
End Sub

Thank you in advance for any help you can provide.

Martha

 

[SkipVought]

Hi,

Do you REALLY want to assign the sheet name every time you change a cell selection on this sheet?

I would make this procedure...

Private Sub AssignSheetName()
  ActiveWorkbook.Unprotect ("password")
  ActiveSheet.Name = ActiveSheet.Range("c6")
  ActiveWorkbook.Protect ("password")
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MJamison07]

Skip,
When I replace what I had with your procedure, the tab name doesn't change when I enter info into c6.

Thanks.

 

[SkipVought]

If that's the case then the code you want is...

Private Sub Worksheet_Change(ByVal Target As Range)

  If Not Intersect(Target, Range("c6")) is nothing then
    ActiveWorkbook.Unprotect ("password")
    ActiveSheet.Name = Range("c6")
    ActiveWorkbook.Protect ("password")
  end if
End Sub

I'd also suggest checking to see if the sheet name is already in use.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MJamison07]

This is exactly what I was hoping for....thank you!