Spreadsheet Conversion to single page format

J

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
Click to expand...
page format. Due to volume, need simplified steps if
possible
 
W

wendyljs

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
 
J

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 [email protected])
<remove NOSPAM>

jeff
-----Original Message-----
Jeff ,
The information is name, address, loan amount, phone
Click to expand...
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.
 
Top