Need an excel geniusClosed

Question

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.

venkat1926 · Answer

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

Need an excel genius

1 response

Similar discussions

Subscribe To Our Newsletter!