Copy/paste multiple cell addresses based on a user-defined numerical value

[Batterychook]

I have reviewed many copy/paste solutions on this forum, but so far haven’t been able to solve my particular problem – although I’m sure there is a simple fix !
I have a need to copy details relevant to individual passengers from a manifest and paste the details into a separate sheet. This will enable later formatting and printing a boarding pass for each passengerfor a joy flight. They are 4 seat aircraft, so in the manifest, the pax are in groups of 3 and allocated to each aircraft/pilot/flight.
The manifest looks like this :

	C	D	E	F	G	H	I	J	K	L

<tbody>
[TD="align: center"]4
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Location :
[/TD]
[TD="align: center"]Texas
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Date :
[/TD]
[TD="align: center"]26/07/2018
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]
[TD="align: center"]Pax #
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Pax Name
[/TD]
[TD="align: center"]Group
[/TD]
[TD="align: center"]Section
[/TD]
[TD="align: center"]Aircraft
[/TD]
[TD="align: center"]Pilot
[/TD]
[TD="align: center"]Flight #
[/TD]
[TD="align: center"]Depart time
[/TD]
[TD="align: center"]WT
[/TD]

[TD="align: center"]11
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]John Doe
[/TD]
[TD="align: center"]Central
[/TD]
[TD="align: center"]junior
[/TD]
[TD="align: center"]ABC
[/TD]
[TD="align: center"]Harry
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]9:20
[/TD]
[TD="align: center"]56
[/TD]

[TD="align: center"]12
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Bob Doe
[/TD]
[TD="align: center"]West
[/TD]
[TD="align: center"]senior
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]64
[/TD]

[TD="align: center"]13
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Steve Did
[/TD]
[TD="align: center"]East
[/TD]
[TD="align: center"]adult
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]52
[/TD]

[TD="align: center"]14
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Jane Zee
[/TD]
[TD="align: center"]Central
[/TD]
[TD="align: center"]junior
[/TD]
[TD="align: center"]DEF
[/TD]
[TD="align: center"]George
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]10:20
[/TD]
[TD="align: center"]70
[/TD]

[TD="align: center"]15
[/TD]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Chris Jay
[/TD]
[TD="align: center"]Central
[/TD]
[TD="align: center"]junior
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]48
[/TD]

[TD="align: center"]16
[/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Evan Blog
[/TD]
[TD="align: center"]East
[/TD]
[TD="align: center"]adult
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]92
[/TD]

[TD="align: center"]17
[/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Jeff Bell
[/TD]
[TD="align: center"]South
[/TD]
[TD="align: center"]junior
[/TD]
[TD="align: center"]XYZ
[/TD]
[TD="align: center"]Alfred
[/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"]11:20
[/TD]
[TD="align: center"]58
[/TD]

[TD="align: center"]18
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Zen Bee
[/TD]
[TD="align: center"]Mid West
[/TD]
[TD="align: center"]senior
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]54
[/TD]

[TD="align: center"]19
[/TD]
[TD="align: center"]9
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Brock Jones
[/TD]
[TD="align: center"]North
[/TD]
[TD="align: center"]teen
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]62
[/TD]

</tbody>

Sheet1​

The cell addresses relating to each passenger (identified byPax #) required to be copied from the manifest are "Pax Name","Group", "Aircraft", "Pilot", "Weight","Location" and "Date". (That is, E11,F11,G11,H11:H13, I11:I13,L11,K4, K6) The H11:H13 and I11:I13 cells are merged.

All of this information can then be pasted into a single row in a separate sheet (Sheet 2). From there it can be formatted for printing the individual boarding passes.

How do I get the user to select or specify a specific Pax # and then copy/paste the associated info relevant to that pax # into a separate sheet ?

Any/all suggestions appreciated !

BC

 

[Batterychook]

With my baby steps in VBA based on my total of 1 weeks knowledge to date.....and with the help of Mr Excel contributors and Google, I've managed to create an input box, search for a Pax # in Column "C" in Sheet 1 and copy/paste the associated row into Sheet 2. (That's a big deal for a newbie)
What I haven't figured out so far, is how to ensure the contents of the three merged cells in Column "H" (that is, H11:H13) and similarly in column "I", are also copied, nor how to select/copy the fixed cell ranges of $K$4 and $K$6 relating to Location and Date.

The code so far is :

Sub Button1_Click()


Dim strsearch As String, lastline As Integer, tocopy As Integer


strsearch = CStr(InputBox("enter the Pax # to Print"))
lastline = Range("C100").End(xlUp).Row
j = 1


For i = 1 To lastline
For Each c In Range("C" & i)
If InStr(c.Text, strsearch) Then
tocopy = 1
End If
Next c
If tocopy = 1 Then
Rows(i).Copy Destination:=Sheets(2).Rows(j)
j = j + 1
End If
tocopy = 0
Next i


End Sub

Any and all help for a struggling newbie is appreciated.
BC