How do I select copy and paste using IF command?

Hello guys.

Just joined in today and have gone through a few of the posts and some solution. You guys are doing a gr8 job. I have a problem, not sure whether a solution has already been given - if so, sorry to bother you all again.
Anyway, my problem, in short:

I have a list of some 120 persons with their personal details in an excel sheet (about 8-9 columns per person). At any given time I need to select 5-9 persons and display their details on another sheet - which I can thereafter print out.

I have self taught myself some of the aspects that I now know about Excel, hence not too sure what I am doing is the ideal way to do things. At present I have used the combo box in the 'forms' tool bar, combined with the 'Offset' formula to serve my purpose. It works, but it has its drawbacks - I have to have 9 such drop boxes, the display inside the drop box cannot be formatted, the search becomes cumbersome - 120+ entries in each drop down, etc.

Instead, I was wondering if I can use an IF command to get my work done. Say I have a blank column before the first column. Against the names that I choose, I insert a number (1-9). Depending on the number, the corresponding cells to the right of the number get copied and pasted on another sheet, in that order. Is that possible? If so, what are the arguements to be used for copy, paste etc in an IF dialogue box? An example would help, if someone has a solution to this.

In case this is not possible, is there any other simple solution - other that manually copying all the data using the control key and then pasting the same in another sheet?

Any suggestions will be most welcome and appreciate your time given for the same.

Regards,

Sanghvi

What you want maybe able to be done via formulas on the 2nd sheet & using a column in sheet1 to tag the names. If it is possible someone else will need to provide the solution


The following is a method using VBA macro

'These instructions pre typed & are worded to cater for the novice programmer
'To install macro to correct location

'Copy this macro
'GoTo Excel
'Select sheet this is to appy to
'Right Click on Sheet Name Tab (sheet with the 120 names) > select View Code
'Paste macro into the Worksheet Module displayed


The macro works whenever you change an entry in sheet1
It will only copy from sheet 1 to sheet 2 if a number is placed in column A
It copies the values only from sheet1 columns B to F from sheet1 & places them on sheet2 in the row that corresponds to the number entered in sheet1 column A

In the code below change sheet2 to the sheet name of your print sheet
If you need more or less columns copied then change
the "e" in Cells(.Value, "e") to another letter - example "d"
and change the 5 in Offset(0, 5)
or if you are not sure on editing the code reply with what columns you need copied

Private Sub Worksheet_Change(ByVal Target As Range)
   With Target
      If .Column = 1 Then
         If Not .Value = "" Then
            If IsNumeric(.Value) Then
               Sheets("sheet2").Range(Cells(.Value, "a").Address, _
                  Cells(.Value, "e").Address).Value _
                  = Range(.Offset(0, 1), .Offset(0, 5)).Value
            End If
         End If
      End If
   End With
End Sub