Renaming sheets

[Nigel]

In
Private Sub CommandButton1_Click()

I have written this code segment to rename "Sheet1" = to the cell value in
"L1" on the same sheet. The Private Sub is located in a different sheet, but
in the same workbook.

Worksheets("Sheet1").Activate
With Worksheets("Sheet1")
.Sheet.Name = Range("L1")
End With

It fails at the third line with "error 438" object does'nt support this
property or method.
Any ideas anyone
Nigel

 

[Mike H]

Nigel,

all in 1 line and no need to slect

Worksheets("Sheet1").Name = Worksheets("Sheet1").Range("L1").Value

Mike

 

[john]

another way:

With Worksheets("Sheet1")
.Name = .Range("L1").Value
End With

 

[Nigel]

Hi JB Should there be a DOT in front of Range ?

another way:

With Worksheets("Sheet1")
.Name = .Range("L1").Value
End With

 

[Mike H]

Yes there should

Hi JB Should there be a DOT in front of Range ?

 

[john]

mike kindly replied.

--
JB

Hi JB Should there be a DOT in front of Range ?

 

[Nigel]

I tried your suggestion like this-
With Worksheets("Sheet1")
.Name = .Range("L1").Value
End With
but it returned run time error 1004, Application-define oro Object defined
error.
When i said should there be a DOT in front of Range i meant in your
suggestion.
Here is the code with the previous execution
Worksheets("Sheet1").Activate
With Worksheets("Sheet1")
.Columns("A:A").ColumnWidth = 16.14
.Columns("B:B").ColumnWidth = 4.29
.Columns("C:C").ColumnWidth = 4.29
.Columns("F:F").ColumnWidth = 4.29
.Columns("I:I").ColumnWidth = 4.29
.Columns("J:J").ColumnWidth = 4.29
.Columns("M:M").ColumnWidth = 4.29
.Columns("P").ColumnWidth = 4.29
.Columns("D").ColumnWidth = 7.57
.Columns("G:G").ColumnWidth = 7.57
.Columns("K:K").ColumnWidth = 7.57
.Columns("N:N").ColumnWidth = 7.57
.Columns("Q:Q").ColumnWidth = 7.57
.Columns("E:E").ColumnWidth = 7.86
.Columns("H:H").ColumnWidth = 7.86
.Columns("L:L").ColumnWidth = 7.86
.Columns("O:O").ColumnWidth = 7.86
.Columns("R:R").ColumnWidth = 7.86
End With
With Worksheets("Sheet1")
.Name = .Range("L1").Value
End With
Range("A1").Select

It returns the same error with or without the DOT
Kind regards Nigel

 

[Nigel]

I tried your suggestion like this-
Worksheets("Sheet1").Name = Worksheets("Sheet1").Range("L1").Value
but it returned run time error 1004, Application-define oro Object defined
error.
When i said should there be a DOT in front of Range i meant in John's
suggestion.
Here is the code with the previous execution
Worksheets("Sheet1").Activate
With Worksheets("Sheet1")
.Columns("A:A").ColumnWidth = 16.14
.Columns("B:B").ColumnWidth = 4.29
.Columns("C:C").ColumnWidth = 4.29
.Columns("F:F").ColumnWidth = 4.29
.Columns("I:I").ColumnWidth = 4.29
.Columns("J:J").ColumnWidth = 4.29
.Columns("M:M").ColumnWidth = 4.29
.Columns("P").ColumnWidth = 4.29
.Columns("D").ColumnWidth = 7.57
.Columns("G:G").ColumnWidth = 7.57
.Columns("K:K").ColumnWidth = 7.57
.Columns("N:N").ColumnWidth = 7.57
.Columns("Q:Q").ColumnWidth = 7.57
.Columns("E:E").ColumnWidth = 7.86
.Columns("H:H").ColumnWidth = 7.86
.Columns("L:L").ColumnWidth = 7.86
.Columns("O:O").ColumnWidth = 7.86
.Columns("R:R").ColumnWidth = 7.86
End With
Worksheets("Sheet1").Name = Worksheets("Sheet1").Range("L1").Value
Range("A1").Select
Kind regards Nigel

 

[john]

nothing as far as I can see wrong with your code but I made two assumptions:

sheet1 exists
range L1 has a value in it that can be used a valid sheet name.

If not, you will get an error.
I altered your code abit.

With Worksheets("Sheet1")
.Activate
.Columns("A:A").ColumnWidth = 16.14
.Columns("B:B").ColumnWidth = 4.29
.Columns("C:C").ColumnWidth = 4.29
.Columns("F:F").ColumnWidth = 4.29
.Columns("I:I").ColumnWidth = 4.29
.Columns("J:J").ColumnWidth = 4.29
.Columns("M:M").ColumnWidth = 4.29
.Columns("P").ColumnWidth = 4.29
.Columns("D").ColumnWidth = 7.57
.Columns("G:G").ColumnWidth = 7.57
.Columns("K:K").ColumnWidth = 7.57
.Columns("N:N").ColumnWidth = 7.57
.Columns("Q:Q").ColumnWidth = 7.57
.Columns("E:E").ColumnWidth = 7.86
.Columns("H:H").ColumnWidth = 7.86
.Columns("L:L").ColumnWidth = 7.86
.Columns("O:O").ColumnWidth = 7.86
.Columns("R:R").ColumnWidth = 7.86

.Name = .Range("L1").Value
.Range("A1").Select

End With

 

[Nigel]

Oh John I am but a fool. Indeed Sheet1 does exist and the current value in
cell L1 is January 2008, so no problem there.
The problem is me. I have been working on this since last night and have
tried to run this code many times including from a Public Sub (macro) which
did work and theres the problem, I now already have a sheet named January
2008.
Once I deleted said sheet your code works fine and as the code will only run
once a month there should never be a problem.

Many many thanks John and to Mike H for all your help.
Nigel

 

[john]

that's what the community is for - thanks for feedback.