Use a List to Generate Worksheets

[Guest]

I have a list of data beginning in cell A1 of Sheet #2 and may be of any
length in column A. I'd like to take that list, and using Sheet #3 as a
template, create Sheet #4 with the data in A1, A2, A3 etc in Sheet #2
transposed into cells B1, C1, D1 etc on sheet #4.

Thanks in advance for the help!

 

[Guest]

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/17/2007
'

'
Sheets("Sheet3").Select
Sheets("Sheet3").Copy After:=Sheets(3)
ActiveSheet.Name = "Sheet4"

LastRow = Sheets("Sheet2"). _
Cells(Rows.Count, "A").End(xlUp).Row
If LastRow > 255 Then LastRow = 255
Sheets("Sheet2").Activate
Set copyrange = Range(Cells(1, "A"), Cells(LastRow, "A"))

copyrange.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet4").Select
Cells(1, "B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
End Sub

 

[Incidental]

Hi Mr. Matt

The code below would be one way of doing it

Option Explicit
Dim LstRow As Integer
Dim WkSh As Worksheet
Dim MyRng As Range

Private Sub CommandButton1_Click()

Sheets(2).Activate

LstRow = [A65535].End(xlUp).Row

Set MyRng = Range("A1:A" & LstRow)

Sheets("Sheet3").Copy After:=Sheets(3)

ActiveSheet.Name = "Sheet4"

MyRng.Copy

[B1].PasteSpecial Transpose:=True

End Sub

Hope this helps

S

 

[Guest]

when you are usingg 2003 there is a limit of the number of columns to 256.
when you transpose 65535 row into columns you get an error. This problem was
noted on the original request when Mr. Matt said the data can be "any Length".

 

[Guest]

I just thought of a good improvement to make the code compatabble witth 2003
and 2007

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/17/2007
'

'
Sheets("Sheet3").Select
Sheets("Sheet3").Copy After:=Sheets(3)
ActiveSheet.Name = "Sheet4"

LastRow = Sheets("Sheet2"). _
Cells(Rows.Count, "A").End(xlUp).Row
If LastRow > (Rows.Count - 1) Then LastRow = Rows.Count - 1
Sheets("Sheet2").Activate
Set copyrange = Range(Cells(1, "A"), Cells(LastRow, "A"))

copyrange.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet4").Select
[B1].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks :=False, Transpose:=True
End Sub

when you are usingg 2003 there is a limit of the number of columns to 256.
when you transpose 65535 row into columns you get an error. This problem was
noted on the original request when Mr. Matt said the data can be "any Length".

Hi Mr. Matt

The code below would be one way of doing it

Option Explicit
Dim LstRow As Integer
Dim WkSh As Worksheet
Dim MyRng As Range

Private Sub CommandButton1_Click()

Sheets(2).Activate

LstRow = [A65535].End(xlUp).Row

Set MyRng = Range("A1:A" & LstRow)

Sheets("Sheet3").Copy After:=Sheets(3)

ActiveSheet.Name = "Sheet4"

MyRng.Copy

[B1].PasteSpecial Transpose:=True

End Sub

Hope this helps

S