Spreadsheet Conversion to single page format

[jeff]

Hi,

It would be helpful to have some idea as to what your
data is like now and how you'd like it reformatted. Will
taking each cell (A1, B1, C1,...) & turning them into
vertical cells (A1, A2,A3...) do what you need? Do you
need a certain number of characters per new line? is
it text? or sets of numbers?

jeff

-----Original Message-----
Trying to convert a single row of information to a full

page format. Due to volume, need simplified steps if
possible

 

[Guest]

Jeff ,
The information is name, address, loan amount, phone number, etc.... set up in one single row, I need to be able to convert this to a single page, but the layout can not look the same. I also have multiple rows (100's), with each row needing to be converted to a single page layout

 

[jeff]

Wendy,

Here is a macro you can past in and run (on a test
worksheet first!!); It assumes the following data layout:

Col A - name (John Doe)
Col B - Street (123 Main St)
Col C - City,St,Zip (MainTown, Ohio 43062)
Col D - Data 1 (ie, loan #)
Col E - Data 2 (ie, loan amount)

I will put these in column C (you can adjust this in
the offset) vertically and put X number of rows of
"padding" between the sets (to get to your page size -
play with the variable "spacerrows"). Add more
"activecell.offset(... ' lines as needed if you have
more data across.


Sub Reformat()
spacerrows = 13
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For j = lastrow To 1 Step -1
Range("A" & j).Select
ActiveCell.Offset(5, 2) = ActiveCell.Offset(0, 4)
ActiveCell.Offset(4, 2) = ActiveCell.Offset(0, 3)
ActiveCell.Offset(3, 2) = ActiveCell.Offset(0, 2)
ActiveCell.Offset(2, 2) = ActiveCell.Offset(0, 1)
ActiveCell.Offset(1, 2) = ActiveCell.Value
ActiveCell.Offset(0, 1) = ""
ActiveCell.Offset(0, 2) = ""
ActiveCell.Offset(0, 3) = ""
ActiveCell.Offset(0, 4) = ""
ActiveCell = ""

For k = 1 To 5 + spacerrows
ActiveCell.EntireRow.Insert
Next k
Next j
End Sub


I hope this helps - if not post back. (or
email me at (e-mail address removed))
<remove NOSPAM>

jeff

-----Original Message-----
Jeff ,
The information is name, address, loan amount, phone

number, etc.... set up in one single row, I need to be
able to convert this to a single page, but the layout can
not look the same. I also have multiple rows (100's),
with each row needing to be converted to a single page
layout.