Validation List choice to copy row from another sheet

[philobr]

Hi there,
I have two similar problems.
The first is that I need to get data copied from sheet1 to sheet2
depending on selection in a validation list.
The data is on the same row and all the same range.
So it would look something like this.
A B C
Fruit Orange Apple Pear
Veg Potato Carrot Onion
Animal Bear Cow Dog

I have the data in A in a dynamic range and validation list.
I need all the data copied from the specific rows ie
choose Animal from the validation list in Sheet2!A1 and
the entire row Bear Cow Dog get copied to Sheet2!B1, Sheet2!C1, Sheet2!D1.

The second problem I think is a lot more complex.
So the same scenario above but this time its a multi select on Sheet3.
I need to be able to select two (or more depending on Fruit and Animals to display in A1, A2 and
then their valid options to appear in B, C and D

I've looked through the board and there doesnt seem to be anything like this so any help will be greatly appreciated.
Thanks

 

[philobr]

Firstly apologies for the ugly code, I ran a record macro. My VBA isnt good enough to write complete complex macros yet.
From the record I got this.

    Application.Goto Reference:="R4C2"
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Range("C5:E5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    Application.Goto Reference:="R4C3"
    ActiveSheet.Paste
    Range("A1").Select

Its nearly doing what I want to solve the first problem, but the

Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate

is an issue. How can I specify that its what I copied from R4C2
and pasted into the search dialog that I want to find rather than "1"?

Also how do you run a macro when an action is done.
In this case when a selection is made from the validation list in R4C2?

 

[xlHammer]

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim x1 As Double
 
If Target.Address = "$B$3" Then
x1 = WorksheetFunction.Match(Target.Value, Sheets("Sheet2").Range("A:A"), 0)
Sheets("Sheet2").Rows(1).Offset(x1 - 1, 0).Copy
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A10")
End If
 
End Sub

B3 is your cell containing the validation list with the categories in it. Assume column A in sheet2 contains the category names.

This code will copy the relevent row from sheet2 and paste it in row 10 on sheet1 when the data validation cell is changed.

Any help?

 

[philobr]

Hi Rob,
I cant seem to get that to work. I have 2003 that wouldnt make a difference would it?

Just so I did everything right
I pasted the code to a new Module
B3 Sheet1 has the validation list (had to use a defined name)
Column A on Sheet2 has the data

I tried changing the validation cell but nothing happened.

I tried changing the code as the sheets are the other way around.
The data is on sheet1 and the validation list on sheet2 but neither way worked.

 

[xlHammer]

You need to paste this into the code area of the sheet itself, not into a module.

In the visual basic editor, on the left hand side, double-click on the sheet that contains the validation list to bring up that sheet's code area and paste the code there.

 

[philobr]

Thats cool, thanks xlHammer

Wouldnt be able to look into the second part of the problem.

I need to be able to do a multi select from the list and do the same copy paste.
If not its cool even just that has saved so much time.

 

[philobr]

xlhammer is it possible to copy everything from the cell to the right of the validation selection.
I tried changing the offset value, but is it the Rows(1) that needs to be changed in

Sheets("Sheet2").Rows(1).Offset(x1 - 1, 0).Copy

 

[mikerickson]

Have you considered a VLOOKUP formula rather than a macro

=VLOOKUP(Sheet2!A1,Sheet1!$A$1:$D$100,Column(),False)

 

[xlHammer]

Is this what you're after?

Sheets("Sheet2").Rows(x1).Resize(1, Rows(x1).Columns.Count - 1).Offset(0, 1).Copy

The second part of your question is getting more complicated, I think you may have to use a list box if you want multiple selections.

 

[philobr]

Hi guys,

mikerickson, the formula is just copying the cell rather than the whole row.

xlhammer, thats exactly what I needed, thanks for this.
I had thought it would be getting very complex with multiple selects and check boxes.

Can I copy/paste and change the references for 10 rows and have 10 validation list or
would I have to have ten separate macros?

Thanks again for this