PDA

View Full Version : Create Index by wrapping columns (multi page)


TheStoneCrusher
Mar 27, 2009, 09:31 AM
I am trying to create a 'book' for karaoke. I have a program that will allow me to read file names into Excel.

The file names contain two columns: one for song title, and one for artist. The problem is that it will be close to 70 pages (10pts). If I could convert with wrap or something to four columns on a page it would be a great help.

I looked at other postings by people who were trying to get two pages into one. Their solutions did not work for me. Can you help? I am using Excel 2002.

Goodwill,
Luke

JBeaucaire
Mar 27, 2009, 10:38 AM
This macro will parse your data in columns A/B into fourths and place in columns D/E, G/H and J/K.

Sub Fourths()
Dim lastrow As Long, nextend As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
nextend = Int(lastrow / 4)

Range("A" & nextend, "B" & nextend * 2).Copy
Range("D2").PasteSpecial xlPasteAll
Range("A" & nextend, "B" & nextend * 2).Delete Shift:=xlUp

Range("A" & nextend, "B" & nextend * 2).Copy
Range("G2").PasteSpecial xlPasteAll
Range("A" & nextend, "B" & nextend * 2).Delete Shift:=xlUp

Range("A" & nextend, "B" & nextend * 2).Copy
Range("J2").PasteSpecial xlPasteAll
Range("A" & nextend, "B" & nextend * 2).Delete Shift:=xlUp
End Sub
Attached is a sample workbook you can test it out on.

To add the macro to your own workbook:
Press Alt-F11 to open the VBEditor
Click Insert > Module
Paste in the code above
Alt-F11 to close the editor
Save your book
Alt-F8 to see the macro list
Run the macro "Fourths"