Rearranging columns/rows

[BernieH]

I have a spreadsheet of the form

NAME BOOK1 BOOK2 BOOK3...
Bob aaa bbbb cccc
Jill ddd eee
Fred fff
Jack ggg hhhh

which I want to reformat into the form

NAME BOOK
Bob aaa
Bob bbb
Bob ccc
Jill ddd
Jill eee
Fred fff

etc

Can anybody suggest the best way to do this?

The no. of BOOKx columns will vary, and because the spreadsheet will be a
regular one, I'll need a macro or such to automate the process

TIA

BernieH

 

[Bob Phillips]

Sub Test()
Dim iLastRow As Long
Dim i As Long, j As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
For j = 3 To Cells(i, Columns.Count).End(xlToLeft).Column
Rows(i + 1).Insert
Cells(i + 1, "A").Value = Cells(i, "A").Value
Cells(i + 1, "B").Value = Cells(i, j).Value
Cells(i, j).Value = ""
Next j
Next i

End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)