formatt question

[Bob Bob]

I have a sheet I am entering information across in rows (A1 is name, B1 is
date C1 is product & D1 is area, then new set of info A2...) I need to view
this information in rows on 1 sheet but in columns but I need the same
information on sheet to in columns so it goes A1 is name, A2 is date A3 is
product & A4 is area, then new set of info B1...) is there a easy way to
change the format from one sheet to the other or a formula that I can do
this with? I have about 300 line of info I need to view per sheet and I
don't want to enter it twice once in rows and then again in columns.
Thanks to all in advance.

 

[Bob Phillips]

It can be done with VBA, but if you have 300 lines and you don't have Excel
2007, it won't fit as you only have 256 columns

 

[Bob Bob]

I can split it up into more work sheets so I am working with 150 at a time.
how do I set up the VBA?

 

[Rick Rothstein \(MVP - VB\)]

If I understand what you want correctly, I think you can do it without VBA.
Go to the sheet with your data and select the 150 rows of your 4-column data
(you can do this easily by typing A1150 in the Name Box... its the blank
box to the right of the Formula Bar). Once the cells are all selected, press
Ctrl+C (or click Edit/Copy in Excel's menu bar) to copy them; then go to a
blank worksheet and click in cell A1; next, click Edit/PasteSpecial in
Excel's menu bar and select Transpose at the bottom of the dialog box that
appeared; and finally click OK. Your first 150 lines of data should now be
copied into that new sheet in the layout you requested. You can repeat the
process for the remaining 150 lines or so of data (use A151300 in the Name
Box for them). By the way, the remaining 150 lines of data do not have to be
placed in their own worksheet... you can put them on the same worksheet as
the first 150 lines of data, just underneath them somewhere; that is, after
you copy them using Ctrl+C, go to the worksheet with the first 150 lines of
data and click in, say, A10 before performing the Paste Special operation.

Rick

 

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim NextCol As Long
Dim sh As Worksheet

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

If i Mod 150 = 1 Then

Set sh =
..Parent.Worksheets.Add(after:=.Parent.Worksheets(.Parent.Worksheets.Count))
NextCol = 1
End If
.Cells(i, "A").Resize(, 3).Copy
sh.Cells(1, NextCol).PasteSpecial Paste:=xlPasteAll,
Transpose:=True
NextCol = NextCol + 1
Next i
End With

End Sub

 

[AltaEgo]

If I read you correctly, you wish to transpose data from one sheet on a
second sheet. Try pasting this formula in cell A1 of a blank sheet and use
the fill tool to copy down and across.

=OFFSET(Sheet1!$A$1,COLUMN()-1,ROW()-1)


Amend 'Sheet1' to the name of the sheet that holds data you wish to
transpose.

 

[AltaEgo]

Oops, forgot the limit and failed when tested. Too fuzzy this morning.

 

[Bob Bob]

Thanks that work perfectly you just save me a whole lot of time.