Excel: Copying a row to a new sheet based on a selection from a drop down menu

Question

I am asking what might be a basic question for Excel / VBA, but I am trying to copy rows from one worksheet (StrategicPlanning) to another worksheet (Experiment) based on what is selected in E7 of Worksheet Experiment. How the Experiment worksheet looks -- please ignore the other cells

My code is not working, and I have a feeling I'm either missing something obvious or I am on the wrong path entirely.

a = Worksheets("StrategicPlanning").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a
    If Worksheets("StrategicPlanning").Cells(i, 5).Value = Worksheets("Experiment").Cells(7, 5) Then
        Worksheets("StrategicPlanning").Rows(i).Copy
        Worksheets("Experiment").Activate
        b = Worksheets("Experiment").Cells(b + 1, 1).Select
        ActiveSheet.Paste
        Worksheets("StrategicPlanning").Activate
    End If

Next

End Sub

I appreciate any and all help! Many thanks!

Thank you; I tried to highlight it so it showed the dropdown arrow but it kept disappearing for the screenshot. I appreciate the link as well and will give it a read! — powdeh, Aug 11 '21 at 00:33
Do you think [AUTOFILTER](https://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) can help you achieve what you want? :) — Siddharth Rout, Aug 11 '21 at 05:11
:-) I have the Autofilter option ready to go if I can't get what I want, but I think I am close on it. Appreciate the recommendation! — powdeh, Aug 11 '21 at 15:07

score 0 · Answer 1 · answered Aug 10 '21 at 23:51

This one uses Worksheet_Change Event, but can be done from a button etc.

Remarks:

I have set Experiment E7 as a "named range" Exp_Title.

Given "codenames" to both sheets: Experiment and StrPlan

CurrentRegion is the same as if you select the cell/range and pressing CTRL + *

You will have to modify to your needs, but will give you an idea. Ask away anything confusing. And definitely read what Chris Neilsen has linked.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not (Intersect(Target, Experiment.Range("Exp_Title")) Is Nothing) Then
        
        Dim rngStrPlan As Range
        
        Set rngStrPlan = StrPlan.Range("A1").CurrentRegion
        Set rngStrPlan = rngStrPlan.Offset(1, 0).Resize(rngStrPlan.Rows.Count - 1, 1)
        
        Dim i As Long
        
        For i = 2 To 2 + rngStrPlan.Rows.Count
        
            If StrPlan.Cells(i, 1).Value = Experiment.Range("Exp_Title").Value Then
                StrPlan.Rows(i).EntireRow.Copy Experiment.Range("A10")
                Exit For
            End If
        
        Next i
        
        Set rngStrPlan = Nothing
        
    End If

End Sub

Thank you! This is helpful; I've been playing with this and my original code and am close to what I want. Appreciate all the help! — powdeh, Aug 11 '21 at 15:05

Excel: Copying a row to a new sheet based on a selection from a drop down menu

1 Answers1