Renaming a merged cell

[camandab]

Hello,

I'm trying to rename a merged cell and I'm getting the following error and the last line of the code is highlighted when I debug:

Run-time error '70' : Permission denied

Sub RenameCell()
'
' '
    Sheets(Sheets.Count).Select
    Cells.Find(what:="general ledger", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Name = "January 2010"
End Sub

I think I might be missing something simple. Any ideas??

 

[Norie]

One idea would be to not use merged cells, though I'm not actually sure the problem described is due to them.

'Permission Denied' sounds more like some sort of file violation/issue.

Another thing that could be causing problems is the use of Select/Selection etc

They just aren't needed and can cause trouble.

 

[camandab]

I should have mentioned that the cell is part of a template and the merged cell is required. Sometimes the merged cells are not always the same (i.e. cannot use Range("A1:E1") as it could also be Range("B1:E1") which is why I try to copy the format of the cell above, paste, and then rename.

I've tried using ActiveCell rather than Selection and another error "That name is not valid".

 

[Norie]

I wouldn't recommend using ActiveCell either.

If you want to search for something then try something like this.

Option Explicit

Sub RenameCell()
Dim rngFnd As Range
    ' '
    With Sheets(Sheets.Count)
        Set rngFnd = .Cells.Find(what:="general ledger", After:=.Range("A1"), LookIn:=xlFormulas _
                                                                                      , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                 MatchCase:=False)
        If Not rngFnd Is Nothing Then
            rngFnd.Copy
            rngFnd.Offset(1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                                          SkipBlanks:=False, Transpose:=False
            rngFnd.Offset(1).Name = "January2010"
        End If
    End With
 
    Application.CutCopyMode = False

End Sub

Oh, and you can't have spaces in range names.

 

[camandab]

Think I figured it out. Thanks for your help Norie!