Converting Spreadsheet into Cover Sheet

M

MikeC

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
 
K

Kiba

If your cover sheet is in a seperate sheet from the data then an equation
like this will work to pull the value from a different sheet in the workbook.
< =Sheet3!D6 >. With this formula (=SheetName!cellreference) if the cell
you refence is blank then it will display a 0. To fix this I would recomend
an if statement such as =if(Sheet3!D6="", "", Sheet3!D6). The "" means
blank. Hope this helps.
 
M

MikeC

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
 
M

MikeC

Thanks!

Kiba said:
If your cover sheet is in a seperate sheet from the data then an equation
like this will work to pull the value from a different sheet in the workbook.
< =Sheet3!D6 >. With this formula (=SheetName!cellreference) if the cell
you refence is blank then it will display a 0. To fix this I would recomend
an if statement such as =if(Sheet3!D6="", "", Sheet3!D6). The "" means
blank. Hope this helps.
Click to expand...
 
G

Gord Dibben

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
 
M

MikeC

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
 
M

MikeC

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
 
G

Gord Dibben

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
 
M

MikeC

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
 
G

Gord Dibben

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 Insert>Module. 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 Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.


Gord
 
M

MikeC

Gord,

Thanks a lot! I have never seen Excel do something like this. It's almost
awe inspiring.

Thanks again!!

Mike
 
M

MikeC

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
 
G

Gord Dibben

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
 
Top