| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Renaming Sheets | Excel Discussion (Misc queries) | |||
| renaming sheets | Excel Programming | |||
| renaming all work-sheets at once | Excel Discussion (Misc queries) | |||
| Renaming sheets | Excel Programming | |||
| renaming sheets | Excel Programming | |||
Linear Mode
