Create Sheet & Copy Stuff

[littleredhairedgirl]

I'm copying and transposing a comma separated list from A1 in Sheet1
to a new sheet. The New sheet is created, but I get an error trying to
paste the list.


Sub AddSheet()
Dim ActNm As String
With ActiveWorkbook.Sheets
..Add after:=Worksheets(Worksheets.Count)
End With
ActNm = ActiveSheet.Name
ActiveSheet.Name = "SheetW"

Dim T As String, S As Variant
ActiveCell.Offset(1, 0).Range("A1").Select
With Worksheets("Sheet1")
T = ActiveCell.Value
End With
S = Split(T, ",")
With Worksheets("SheetW")
.Range("A1").Resize(UBound(S) + 1) = Application.Transpose(S)
End With
End Sub

 

[p45cal]

Your looking for something like this I think:

Sub AddSheet()
Dim T As String, S As Variant
Set NewSheet
ActiveWorkbook.Sheets.Add(after:=Worksheets(Worksheets.Count))
NewSheet.Name = "SheetW"
Worksheets("Sheet1").Activate
ActiveCell.Offset(1, 0).Select
T = ActiveCell.Value
S = Split(T, ",")
NewSheet.Range("A1").Resize(UBound(S) + 1) = Application.Transpose(S)
End Sub

but you can shorten it a bit by removing T:

Sub AddSheet2()
Dim S As Variant
Set NewSheet
ActiveWorkbook.Sheets.Add(after:=Worksheets(Worksheets.Count))
NewSheet.Name = "SheetW"
Worksheets("Sheet1").Activate
ActiveCell.Offset(1, 0).Select
S = Split(ActiveCell.Value, ",")
NewSheet.Range("A1").Resize(UBound(S) + 1) = Application.Transpose(S)
End Sub



Some comments added in the original message code below.