ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Renaming sheets (https://www.excelbanter.com/excel-programming/407079-renaming-sheets.html)

Nigel
Renaming sheets
 
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
Renaming sheets
 
Nigel,

all in 1 line and no need to slect

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

Mike

"Nigel" wrote:

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

John
Renaming sheets
 
another way:

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


"Nigel" wrote:

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

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

"john" wrote:

another way:

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


"Nigel" wrote:

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
Renaming sheets
 
Yes there should

"Nigel" wrote:

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

"john" wrote:

another way:

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


"Nigel" wrote:

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

John
Renaming sheets
 
mike kindly replied.

--
JB


"Nigel" wrote:

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

"john" wrote:

another way:

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


"Nigel" wrote:

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

Nigel
Renaming sheets
 
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:P").ColumnWidth = 4.29
.Columns("D: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
"john" wrote:

mike kindly replied.

--
JB


"Nigel" wrote:

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

"john" wrote:

another way:

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


"Nigel" wrote:

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

Nigel
Renaming sheets
 
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:P").ColumnWidth = 4.29
.Columns("D: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

"Mike H" wrote:

Yes there should

"Nigel" wrote:

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

"john" wrote:

another way:

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


"Nigel" wrote:

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

John
Renaming sheets
 
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:P").ColumnWidth = 4.29
.Columns("D: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


--
JB


"Nigel" wrote:

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:P").ColumnWidth = 4.29
.Columns("D: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
"john" wrote:

mike kindly replied.

--
JB


"Nigel" wrote:

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

"john" wrote:

another way:

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


"Nigel" wrote:

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

Nigel
Renaming sheets
 
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" wrote:

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:P").ColumnWidth = 4.29
.Columns("D: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


--
JB


"Nigel" wrote:

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:P").ColumnWidth = 4.29
.Columns("D: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
"john" wrote:

mike kindly replied.

--
JB


"Nigel" wrote:

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

"john" wrote:

another way:

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


"Nigel" wrote:

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

John
Renaming sheets
 
that's what the community is for - thanks for feedback.
--
JB


"Nigel" wrote:

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" wrote:

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:P").ColumnWidth = 4.29
.Columns("D: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


--
JB


"Nigel" wrote:

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:P").ColumnWidth = 4.29
.Columns("D: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
"john" wrote:

mike kindly replied.

--
JB


"Nigel" wrote:

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

"john" wrote:

another way:

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


"Nigel" wrote:

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


All times are GMT +1. The time now is 06:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com