We can transpose the three columns A:C into one long column then insert a
pagebreak after every third row(note alteration to InsertBreak)
The formatting of each page for printing I will leave to you. You may want to
fiddle with increased row heights, centered text, horizontal and vertical
placement etc.
Sub BuildColumn()
'build one column from 3 columns
Dim myRange As String
Dim cell As Range, i As Integer
Application.ScreenUpdating = False
i = 1
For Each cell In Range("A1:C702")
Range("E1").Cells(i) = cell
i = i + 1
Next
Range("A:D").ClearContents
Application.ScreenUpdating = True
InsertBreak
End Sub
Sub InsertBreak()
'insert a pagebreak every third row
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
numRows = 1
For r = 702 To 1 Step -3
ActiveSheet.Rows(r + 1).Resize(numRows) _
.PageBreak = xlPageBreakManual
Next r
Application.ScreenUpdating = True
End Sub
Gord
On Tue, 22 May 2007 09:09:01 -0700, MikeC
wrote:
I don't mean to be a pain, but I'm trying to learn all the functions of
Excel, or as much as I can fit in. The macro works great, I have 702 pages,
and thats awesome. However, is there anyway of breaking up the row to fit in
perhaps a lab report format?
like...
Title (A1)
Date (B1)
Who performed it (C1)
and so on........
all of this data is in one row, and now on its own page. So please let me
know if this is possible. Thanks for your patience.
MikeC
"MikeC" wrote:
Gord,
Thanks a lot! I have never seen Excel do something like this. It's almost
awe inspiring.
Thanks again!!
Mike
"Gord Dibben" wrote:
Mike
Here is a macro that will insert a pagebreak every row.
I would suggest running this macro on a copy of your worksheet or workbook.
When you print the worksheet you will get 702 printed pages with one row on each
page.
Sub InsertBreak()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim r As Long
numRows = 1
For r = 702 To 1 Step -1
ActiveSheet.Rows(r + 1).Resize(numRows) _
.PageBreak = xlPageBreakManual
Next r
Application.ScreenUpdating = True
End Sub
If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".
http://www.mvps.org/dmcritchie/excel/getstarted.htm
In the meantime..........
First...create a backup copy of your original workbook.
To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
Hit CRTL + r to open Project Explorer.
Find your workbook/project and select it.
Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.
Run or edit the macro by going to ToolMacroMacros.
You can also assign this macro to a button or a shortcut key combo.
Gord
On Mon, 21 May 2007 12:54:01 -0700, MikeC
wrote:
Yes, Gord, as sad as it is, I need to do that. I am an intern, and I do as
I'm told. If you could help me as to explain how to take each cell and place
it on a cover sheet without copying and pasting, that would be beyond
helpful. Thanks in advance, and by the way, I'm in Illinois, nothing but
corn for miles.
Thanks,
MikeC
"Gord Dibben" wrote:
I'm still confused........easily confused is my forte.
What do you consider a "cover sheet"?
A printed page with only A1:E1 on first page and A2:E2 on second page and so on
for 702 pages?
Could be done but that's a great lot of trees<g
I'm up here in Beautiful British Columbia and we're running short unless you
want Pine Beetle-killed pulp.
Gord
On Mon, 21 May 2007 11:55:01 -0700, MikeC
wrote:
The 702 cover sheets is what I am looking for...so if there's anyway of
simplifying this by perhaps a macro? Please let me know.
Thanks,
Mike
"MikeC" wrote:
What I was thinking was, making a coversheet for every row in my spread
sheet, where columns are different parts of the coversheet. For example, A1
would be the title, B1 would be the subtitle, C1 would be what was done...and
so on to E1, and repeat the process for row 2 and continued to row 702. Does
that make more sense? I'm sorry for the confusion. Let me know what everybody
thinks. Thanks a lot.
MikeC
"Gord Dibben" wrote:
Mike
Re-think what you you're asking and re-post.
A1:A702 as a title would take about 12 or 13 pages to show.
A cover sheet for each row would give you 702 cover sheets.
Gord Dibben MS Excel MVP
On Fri, 18 May 2007 08:38:02 -0700, MikeC
wrote:
Perhaps I should be a little more specific. An example would include A1:A702
would be the title, and every corresponding row would be a different cover
sheet. Thanks in advance.
MikeC
"MikeC" wrote:
I want to take my spreadsheet and take each column and place it on different
areas of a cover sheet. Is this even possible? For example, A1 would be the
title...E1 would be the abstract, something of this nature. Your help is
greatly appreciated.
Thanks,
MikeC