Prepare a list in excel for printing  
Author Message
nsheff





PostPosted: Visual Basic for Applications (VBA), Prepare a list in excel for printing Top

Hi,

I have a two column list in excel with about six thousand entries. I want to be able to print it out in four columns. I am trying to write a macro that would go down the list for 47 rows and print it into the first column and then go down another 47 rows and print it into the next column etc. After the first four columns are done I want to offset the printable list down about five rows and do it all over again. I just can't seem to understand the range and offset routines to help me do this. Can anyone give me a quick looping macro Would appreciate it.

would help)



Microsoft ISV Community Center Forums1  
 
 
Cindy Meister





PostPosted: Visual Basic for Applications (VBA), Prepare a list in excel for printing Top


I'm not sure how easily 6000 rows will come across, but I'd certainly
try copying or linking the table into a Word document formatted with two
(newspaper) columns. That should more or less do what you're looking for
without needing any VBA code.


 
 
ChasAA





PostPosted: Visual Basic for Applications (VBA), Prepare a list in excel for printing Top

Hello nsheff,

The following should do it.

Your original data needs to be in two columns in Sheet1

If you two balnk columns in your data, the macro will stop.

The reformatted data will be placed in Sheet2.

You can change the names of the sheets accordingly.

The "on error" is a lazy way to stop the macro when complete, with a bit of thought this can be done more neatly.

Code below.

Chas

Sub PrepareForPrint()
Dim off As Long
Dim printOff As Long
Dim thisCol As String
Dim nextCol As String
Dim thirdCol As String
Dim fourthCol As String
Dim counter As Long
Dim pageLength As Integer
Dim gapBetweenPages As Integer

On Error GoTo errorHandler:
' turn screen updating off to speed up macro
Application.ScreenUpdating = False

Sheets("Sheet1").Select
Range("A1").Select
Selection.CurrentRegion.Select

' read the range into an array

printItem = Selection.Value
Sheets("Sheet2").Select
Range("A1").Select
thisCol = printItem(1, 1)
nextCol = printItem(1, 2)
pageLength = 47 ' set lines per page
gapBetweenPages = 5 ' lines between pages
Do While thisCol <> "" And nextCol <> ""
' point to next array element
' first time around is the same as above

off = off + 1
printOff = (printOff + 1) - 1 ' cell offset to write to is -1

'set the four variable to write
thisCol = printItem(off, 1)
nextCol = printItem(off, 2)
thirdCol = printItem(off + pageLength, 1)
fourthCol = printItem(off + pageLength, 2)
' write these variable to sheet
Selection.Offset(printOff, 0) = thisCol
Selection.Offset(printOff, 1) = nextCol
Selection.Offset(printOff, 2) = thirdCol
Selection.Offset(printOff, 3) = fourthCol
printOff = printOff + 1
counter = counter + 1
If counter = pageLength Then
'put spacing between pages
printOff = printOff + gapBetweenPages
counter = 0
' point to next element in array
off = off + pageLength
End If

Loop

Exit Sub

errorHandler:
If Err.Number = 9 Then
Application.ScreenUpdating = True
MsgBox ("Done")
End If


End Sub


 
 
ChasAA





PostPosted: Visual Basic for Applications (VBA), Prepare a list in excel for printing Top

Hello again,

I phrased one of the lines incorrectly:

"If you two balnk columns in your data, the macro will stop."

If you have a blank row within you data range, the macro will stop

Chas