If you look at that code, it really doesn't do too much.

It groups in sets of 20 rows and copies and pastes|transpose.

Then a little bit of clean up.

If you have questions about anything, post back. I'm sure you'll get lots of
answers.

Rich F wrote:

Dave,

Worked like a charm and with the little knowledge I have of VBA that I've
been able to pick up from Access (and disecting macros in the past), I think
I might even sort of have a slight inklining of how/why this works and what I
don't understand, I'm looking in a VBA book I've gotten access to on-line
through my employer.

Thanks again--you've given me back my weekend!

--
Rich F

"Dave Peterson" wrote:

You can try something like this.

Option Explicit
Sub testme()
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HowManyRowsPerGroup As Long

With Worksheets("sheet1")
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
HowManyRowsPerGroup = 20

'HowManyRowsPerGroup + 2 because of the two blank rows
For iRow = FirstRow To LastRow Step HowManyRowsPerGroup + 2
.Cells(iRow, "B").Resize(HowManyRowsPerGroup, 1).Copy
.Cells(iRow, "C").PasteSpecial Transpose:=True
Next iRow

'copy headers
.Rows(1).Insert
.Cells(2, "A").Resize(HowManyRowsPerGroup, 1).Copy
.Cells(1, "C").PasteSpecial Transpose:=True

On Error Resume Next
.Range("c:c").Cells.SpecialCells(xlCellTypeBlanks) .EntireRow.Delete
On Error GoTo 0

.Range("a:b").Delete

.UsedRange.Columns.AutoFit
End With

End Sub

But try it against a copy of your data--it destroys the original data when it
runs.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Rich F wrote:

I've combed through previous posts and I can't find exactly what I'm looking
for, so here goes:

I've got a worksheet with approx 1000+ employees set up in the following
way, repeated as rows:

A B
1 Type Jones
2 FirstName John
3 MiddleInitial
4 LastName Jones
5 FullName John Jones
6 EnterpriseID John.Jones
7 GMUNumber 333
8 GMUDescription Acme Inc
9 LMUNumber 9IJ
10 LMUDescription Special Task Grp
11 DISCIPLINE_CD 70
12 DISCIPLINE_DESC_TEXT Services/Client
13 Position 7778787
14 PositionName Level E
15 Location Timbuktu
16 CostCenterNum 67676766
17 CostCenterDesc Antedilluvian
18 PersonnelNumber 18976565
19 PeopleKey 126767
20 UserType N/A

All the "employee record clumps" have the same number of rows and there are
two lines between each "clump." I'm trying to transpose the info, so the
items in COLUMN A are column headings (there would be 20) and then have each
of employee's data fall into place under each correct column heading. PASTE
SPECIAL/TRANSPOSE doesn't work, unless I do EACH employee seperatly and I
don't, unfortunately, have two or three weeks to cut/paste the 1000+ emps
this way. Is there any other way to do it?

Thanks in advance for the help!
--
Rich F

--

Dave Peterson


--

Dave Peterson