Need help re-organizing data

[Sorbital]

First, this is an awesome site. You provide an invaluable service the Excel community, I bow to your knowledge!!

With that said, here's my issue I need help with.

I get an excel workbook that has 45-50 sheets listed at the bottom. Each sheet has one person's info, so basically info on 45-50 people.

Row 1 has static values. Name, Address, phone number, etc. Row 2 has the data I need. Each sheet has just one person's data.

What I need to do is be able to take the static values and apply them to Column A (going from a horizontal approach to a vertical), have the data show up in Column B. If possible, column C (and so on) would have the data from the other sheets in the work book.

I don't mind printing a bunch of sheets if the data has to stay as one person per sheet, just want to go from horizontal to verticle without cut/paste the data.

Is there a way that I can save the original woorkbook as a CSV and then import the data in a different way? Is there a macro I can create that will re-org the data?

Any help would be welcome and greatly appreciated!!

 

[jim may]

Can you layout for us a typical JimsSheet

and also the Master sheet containing data from at least 2 sheets.

 

[mooseman]

From your post, It looks like the data is in this format:

Sheet 1:
Name Address phone number
John Doe 123 main st 555-5558

Sheet 2:
Name Address phone number
Jane Doe 321 Maple Ave 633-1212

Sheet 3:
Name Address phone number
Adam Eve 789 elm dr 999-7777


And you want it to be all in one sheet like this:

Name John Doe Jane Doe Adam Eve
Address 123 main st 321 Maple Ave 789 elm dr
phone number 555-5558 633-1212 999-7777


IF so, then this code should work. It's not elegant or optimized, but I think it's what you are asking for.

Sub Transpose_multisheets()
'activate first sheet in workbook
      Sheets(1).Activate
      
' Insert new worksheet
      Sheets.Add
'Name it master
      Sheets(1).Name = "Master"
      
'Copy and Paste from headings from row 1 to Master sheet and transpose
Sheets(2).Activate
Rows(1).Copy
Sheets(1).Activate
Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
        
'Now copy and paste data from each sheet and transpose

For i = 2 To Sheets.Count
Sheets(i).Activate

'gets the range of cells that are populted with data
rng = "A2:" & ActiveCell.SpecialCells(xlLastCell).Address

Range(rng).Copy

Sheets("Master").Activate
    'moves to the next column
    ActiveCell.Offset(0, 1).Select
    'pastes and tramsposes the copied data
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
Next i

End Sub

I'll check back on Monday....

 

[stanleydgromjr]

Sorbital,

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4.
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php


Have a great day,
Stan

 

[Sorbital]

Mooseman, you are a GOD!

That worked perfectly and produced EXACTLY what I needed, you made me look like a friggin star and saved hours of work for me.

Thank you more then you can know!!!

 

[mooseman]

I aim to please
But, really, others on this board are so much better. I just do my part.