How to format a printout?

[Ilya Zeldes]

I exported a few hundred contacts from my Windows Live Mail as CSV file and
opened it in Excel 2002. Of course, I can easily print this spreadsheet as
it is, where each row contains all information about each entry. However,
I'd like to print this information in a form of an address book:

Last Name, First Name
email address
Street Address, City, Zip
Home Phone
Business phone

Last Name, First Name
email address
etc.

Is there a way to do so?

 

[Gord Dibben]

How is the original data laid out?

All in one cell?

In one row but separate cells?

Is Last name, First name in one cell or two?

How about Street Address, City, Zip?

If you wish, send me a copy of the workbook.

gorddibbATshawDOTca change the obvious for ny email address.


Gord Dibben MS Excel MVP

 

[Ilya Zeldes]

Gord,

in this workbook (just one sheet), each row contains one entry. First
cell -Last Name, second cell - First Name, etc., each cell contains just one
item: street address, city, state, zip...

Ilya.

 

[Gord Dibben]

Rather than explain the process here, how about you send me the workbook?

I'll transpose it all for you and show you how I did it.

gorddibbATshawDOTca


Gord

 

[Ilya Zeldes]

Frankly, Gord, I'm hesitant to share my address book with anyone. I presume
and hope that there is a way to do what I want and it is described
somewhere. If you may direct me to that source, I'd appreciate it.

Ilya

 

[Gord Dibben]

I appreciate your concern about sharing your address book.

I may be a spammer/scammer who trolls for new victims.

First of all you must concatenate the fields you want in one cell.

=A1 & ", " & B1 will return Lastname, Firstname.

Same for Street Address, City, State, Zip.

Do this in an unused range of your sheet..

Arrange the email, phone, phone columns as you want.

You now have five adjacent columns.

Copy those and paste special>values to a new sheet at A1

Run this macro on your 5 columns.

Sub rows_to_col()

Dim wks As Worksheet
Dim colnos As Long
Dim CopytoSheet As Worksheet

If ActiveSheet.Name = "Copyto" Then
MsgBox "Active Sheet Not Valid" & Chr(13) _
& "Try Another Worksheet."
Exit Sub
Else
Set wks = ActiveSheet
Application.ScreenUpdating = False
For Each Wksht In Worksheets
With Wksht
If .Name = "Copyto" Then
Application.DisplayAlerts = False
Sheets("Copyto").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "Copyto"
wks.Activate
Range("A1").Select
colnos = InputBox("Enter Number of Columns to Transpose to Rows")

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
With ActiveCell
.Resize(1, colnos).Copy
End With
Sheets("Copyto").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
ActiveCell.Offset(2, 0).Select
Selection.EntireRow.Insert
wks.Activate
ActiveCell.Select
Loop
Sheets("Copyto").Activate
End If
End Sub


Gord

 

[Ilya Zeldes]

Thanks for your suggestions.

Ilya

I appreciate your concern about sharing your address book.

I may be a spammer/scammer who trolls for new victims.

First of all you must concatenate the fields you want in one cell.

=A1 & ", " & B1 will return Lastname, Firstname.

Same for Street Address, City, State, Zip.

Do this in an unused range of your sheet..

Arrange the email, phone, phone columns as you want.

You now have five adjacent columns.

Copy those and paste special>values to a new sheet at A1

Run this macro on your 5 columns.

Sub rows_to_col()

Dim wks As Worksheet
Dim colnos As Long
Dim CopytoSheet As Worksheet

If ActiveSheet.Name = "Copyto" Then
MsgBox "Active Sheet Not Valid" & Chr(13) _
& "Try Another Worksheet."
Exit Sub
Else
Set wks = ActiveSheet
Application.ScreenUpdating = False
For Each Wksht In Worksheets
With Wksht
If .Name = "Copyto" Then
Application.DisplayAlerts = False
Sheets("Copyto").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "Copyto"
wks.Activate
Range("A1").Select
colnos = InputBox("Enter Number of Columns to Transpose to Rows")

Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
With ActiveCell
.Resize(1, colnos).Copy
End With
Sheets("Copyto").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False
ActiveSheet.Cells(Rows.Count,
ActiveCell.Column).End(xlUp).Select
ActiveCell.Offset(2, 0).Select
Selection.EntireRow.Insert
wks.Activate
ActiveCell.Select
Loop
Sheets("Copyto").Activate
End If
End Sub


Gord