D
davegb
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.
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.