Need an excel genius

Closed
Report
Posts
1
Registration date
Wednesday November 18, 2009
Status
Member
Last seen
November 19, 2009
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Howdy. I hope you guys can shed some light on a tricky situation and I shall try to explain best I can.

I have an excel document where one of the tabs has a list of locations, phone and fax numbers. 3 columns in total and about 300 entries.
I have been tasked with sending out a fax cover sheet to all 300 units, joy. I was wondering, is there a way of creating a cover sheet so that it displays information from the excel sheet?

Here is an example. My excel sheet has data thus:

Name Phone Fax
Name1 45846658 6598584
Name2 49659995 6548894

But I want a cover sheet to contain information in this way:

My name

Message to person

Persons unit (taken from Name1)
Persons Phone and fax taken from the data in row 1.

Signed.

Now thats easy, I can just point excel where I want the information to come from. But I have to print this cover sheet to all 300 people, meaning 300 unique cover sheets. So can I have a button or something whereby if I press it, the next unit Name2 is displayed along with its phone and fax number. So all I have to do it press print, hit a button and press print again. I know its 300 presses but rather that than have to copy and paste over 300 times instead.

I know its a complicated mess of a situation, but any help given would be amazing.

1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
I have given you an EVENT CODE
modify it if necessary

what it does is when you select any name in range A2 down (row 1 has column headings) in sheet 1 automatically some entries are made in sheet 2 (which is your cover sheet)
if you select another name in sheet 1 column A correspondingly sheet 2 will be changed.
if you select a blank cell nothing happens that is sheet 2 will be blank
the main data in sheet 1 is arranged like this from A1 down and to right
row 1 contains headings. and rest data in the same configuration as you have given in your message.

that is A2 name , B2 phone number, C2 fax like this down.

CONFIRM WHETHER IT IS OK OR YOU NEED SOME CHANGES

How to park this event code
Right click sheet1 tab (repeat sheet 1 tab) and click "view code" . In the window that comes up copy paste the following event code and then test
In the code there is a statement

myname = "xxxxxxxx"


chnge this xxxx into your name

the event code is

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myname As String
With Application
.EnableEvents = False
.ScreenUpdating = False
If Target.Column <> 1 Then GoTo eexit
If Target.Address = "$A$1" Then GoTo eexit
If Target = "" Then
Worksheets("sheet2").Cells.Clear
GoTo eexit
End If
If Err.Number <> 0 Then
MsgBox "error occured" & " " & "errornumber=" & Err.Number
GoTo eexit
End If
myname = "xxxxxxxx"
With Worksheets("sheet2")
.Cells.Clear
.Range("a1") = myname
.Range("a3") = "message to person"
.Range("a5") = Target
.Range("B5") = Target.Offset(0, 1)
.Range("C5") = Target.Offset(0, 2)

.Range("a9") = "signed"
End With
eexit:
.ScreenUpdating = True
.EnableEvents = True
End With 'application


End Sub
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month