Tricky (for me) VBA copy and paste.

[SensualCarrots]

I have two data validation lists. The second is dependent on the first. So let's go with the old fruit and vegetable tables. First list, my choices are Fruit or Vegetable. The second list is dependent on the first. Let's say if you select Fruit, your choices are Apple, Orange, and Pear in the second list. But if you select vegetable, your choices are Carrot, Celery, and Broccoli. On another page, I have the tables where the choices of fruits and vegetables are located. In the cell directly to the right of the fruit or vegetable, I have a range of cells defined as text. For example, the cell next to Apple is "A1:O23", and the cell next to Orange is "A24:O42", and so forth. When the two drop down boxes are populated, and a button is clicked, I need to copy the range in the adjacent cell, and paste the values of that range into the next blank cell in a given column on the first page. I was trying to do index matching, but doing it in VBA is ugly. Any advice?

 

[Fazza]

hi

give the ranges the names of the fruit/vegetable: such as the range referred to by text "A1:O23", next to apple, has defined name apple

then the code could be something like below. please modify to suit requirements. HTH

Sub usage()

    Call PasteDataFromRangeToColumn(SourceRangeName:="apple", DestinationColumn:=1)
    
End Sub

Function PasteDataFromRangeToColumn(ByVal SourceRangeName As String, ByVal DestinationColumn As Long)

    With Range(SourceRangeName)
        Cells(Rows.Count, DestinationColumn).End(xlUp).Offset(1).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

End Function