The following code takes and existing worksheet, transposes the sheet,
then creates a separate workbook for each of the columns in the
transposed worksheet. I want to name each workbook from the
corresponding column header, but XL gives me a compile error that the
workbook name is a read-only property? Why?

Option Explicit
Sub Transpose2NewSht()
Dim wbCty As Workbook
Dim sNew As String
Dim lCurCol As Long
Dim wsSource As Worksheet
Dim wsTranspose As Worksheet
Dim sCty As String
Dim lStrDif As Long

Set wsSource = ActiveSheet

lCurCol = 2

wsSource.Range("A1").Select

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Selection.Copy
Sheets.Add.Activate
Set wsTranspose = ActiveSheet
wsTranspose.Name = "Transpose"

Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True

Do Until wsTranspose.Cells(1, lCurCol) = ""

sCty = wsTranspose.Cells(1, lCurCol)
lStrDif = Len(sCty) - 4
sCty = Right(sCty, Len(sCty) - lStrDif)

Range("A1:A4").Select
'Application.CutCopyMode = False
Selection.Copy
Workbooks.Add.Activate
Set wbCty = ActiveWorkbook
wbCty.Name = sCty <----- READ ONLY PROPERTY

Any suggestions?
Thanks in advance.

Yuo can't change the name of a file when it is opend. You Excel file is
opened therefore you can't change the name. Use Saveas to save the file,
then the name will automatically change.

"davegb" wrote:

The following code takes and existing worksheet, transposes the sheet,
then creates a separate workbook for each of the columns in the
transposed worksheet. I want to name each workbook from the
corresponding column header, but XL gives me a compile error that the
workbook name is a read-only property? Why?

Option Explicit
Sub Transpose2NewSht()
Dim wbCty As Workbook
Dim sNew As String
Dim lCurCol As Long
Dim wsSource As Worksheet
Dim wsTranspose As Worksheet
Dim sCty As String
Dim lStrDif As Long

Set wsSource = ActiveSheet

lCurCol = 2

wsSource.Range("A1").Select

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Selection.Copy
Sheets.Add.Activate
Set wsTranspose = ActiveSheet
wsTranspose.Name = "Transpose"

Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True

Do Until wsTranspose.Cells(1, lCurCol) = ""

sCty = wsTranspose.Cells(1, lCurCol)
lStrDif = Len(sCty) - 4
sCty = Right(sCty, Len(sCty) - lStrDif)

Range("A1:A4").Select
'Application.CutCopyMode = False
Selection.Copy
Workbooks.Add.Activate
Set wbCty = ActiveWorkbook
wbCty.Name = sCty <----- READ ONLY PROPERTY

Any suggestions?
Thanks in advance.

The workbook name is the file name and ultimately that is controlled by the
operating system. What you need to do is to save the file using the new name.

wbCty.SaveAs FileName:= thisworkbook.path & "\" & sCity
--
HTH...

Jim Thomlinson


"davegb" wrote:

The following code takes and existing worksheet, transposes the sheet,
then creates a separate workbook for each of the columns in the
transposed worksheet. I want to name each workbook from the
corresponding column header, but XL gives me a compile error that the
workbook name is a read-only property? Why?

Option Explicit
Sub Transpose2NewSht()
Dim wbCty As Workbook
Dim sNew As String
Dim lCurCol As Long
Dim wsSource As Worksheet
Dim wsTranspose As Worksheet
Dim sCty As String
Dim lStrDif As Long

Set wsSource = ActiveSheet

lCurCol = 2

wsSource.Range("A1").Select

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Selection.Copy
Sheets.Add.Activate
Set wsTranspose = ActiveSheet
wsTranspose.Name = "Transpose"

Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=True

Do Until wsTranspose.Cells(1, lCurCol) = ""

sCty = wsTranspose.Cells(1, lCurCol)
lStrDif = Len(sCty) - 4
sCty = Right(sCty, Len(sCty) - lStrDif)

Range("A1:A4").Select
'Application.CutCopyMode = False
Selection.Copy
Workbooks.Add.Activate
Set wbCty = ActiveWorkbook
wbCty.Name = sCty <----- READ ONLY PROPERTY

Any suggestions?
Thanks in advance.

On Wed, 23 Apr 2008 08:43:04 -0700, Jim Thomlinson wrote:

The workbook name is the file name and ultimately that is controlled by the
operating system. What you need to do is to save the file using the new name.

wbCty.SaveAs FileName:= thisworkbook.path & "\" & sCity

Thanks, Jim, worked like a charm!