0

Update

I was able to get the code to run, but I still think i'm doing something wrong. It's not copying and pasting the right value, just pasting the original value over and over. New code below.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    Set KeyCells = Range("X2")
    Dim rb As Worksheet
    Set rb = ThisWorkbook.Worksheets("RB_Cur")
    Dim dp As Worksheet
    Set dp = ThisWorkbook.Worksheets("Dispatch Plan")

    
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
    Is Nothing Then
    dp.Range("V4:V16").ClearContents
        With rb.Range("E1:AA1000" & rb.Range("E" & Rows.Count).End(xlUp).Row)
            .AutoFilter Field:=1, Criteria1:=dp.Range("X2").Value
            .AutoFilter Field:=7, Criteria1:=dp.Range("W1").Value
        If .SpecialCells(xlCellTypeVisible).Count > 0 Then
        rb.Range("AA1").Offset(1).Resize(10, 1).Copy
        dp.Range("V4").PasteSpecial xlPasteValues
        End If

    End With
End If
End Sub

Original Post

I am trying to create a table where someone can input a route number into a cell and it will populate with the stops on that route. However, i'm running into some trouble getting the stop list to populate when the cell value is changed.

Picture example: When the highlighted cell (X2) is changed, I have a code that searches it in a different sheet and copies over the stops into the Stop column. The code to lookup and copy works, but it doesn't change when the number is changed.

enter image description here

RouteLookup code (this works)

Sub RouteLookup()

Dim rb As Worksheet
Set rb = ThisWorkbook.Worksheets("RB_Cur")
Dim dp As Worksheet
Set dp = ThisWorkbook.Worksheets("Dispatch Plan")

  With rb.Range("E1:AA1000" & rb.Range("E" & Rows.Count).End(xlUp).Row)
    .AutoFilter Field:=1, Criteria1:=dp.Range("X2").Value, Criteria1:="EARLY BIRD"
    If .SpecialCells(xlVisible).Count > 0 Then
      rb.Range("AA1").Offset(1).Resize(10, 1).Copy Destination:=dp.Range("V4")
    End If
    .AutoFilter Field:=1
  End With


End Sub

Worksheet SelectionChange code - Whenever I try to test it, it pops up a box asking me to select the macro. I don't understand why because the code calls the RouteLookup already.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, Target.Worksheet.Range("X2")) Is Nothing Then
        Application.EnableEvents = False
        Call RouteLookup
        Application.EnableEvents = True
    End If
End Sub
QRLess
  • 23
  • 5
  • as per this [link](https://stackoverflow.com/questions/2804327/call-a-subroutine-from-a-different-module-in-vba) is routelookup unique in the workbook? if you just put the code of routelookup into worksheet_selectionchange does it work? – InjuredCoding Jul 04 '22 at 20:25
  • I tried that too but it didn't run. It still asked me to select a macro. – QRLess Jul 04 '22 at 20:31
  • sorry which part did you try? have you tried replacing `Call RouteLookup` with the code from in the routelookup sub? – InjuredCoding Jul 04 '22 at 20:34
  • as an aside the selection change code code works, I'm a little surprised you'd want to run this on selection change not on value change but that's up to you! i think your issue is it can't find Routelookup. Put it in the same sheet code and see if it works then move to a new module in the workbook then where ever you have it. you'll see at what point excel loses it – InjuredCoding Jul 04 '22 at 20:44
  • I did try putting the code from routelookup into the sub but it still wasn’t working. If there’s a better way than selection change please let me know! I was only using it because it’s what I saw the most while researching – QRLess Jul 05 '22 at 03:37
  • so you put `Dim Rb......` all the way to `End with` in instead of `Call RouteLookup` you clicked in cell x2 of the sheet and it threw an error? what error did it throw this time? (note i think i would use `Private Sub Worksheet_Change(ByVal Target as Range) ` instead [link](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.change)) – InjuredCoding Jul 05 '22 at 08:38
  • No specific error, it just keeps popping up asking me to select a macro. – QRLess Jul 05 '22 at 19:10
  • Regarding your new code in the post, how about try to step run it and see if your range to be copied is correct by putting a "helper" code something like this `rb.Range("AA1").Offset(1).Resize(10, 1).select` right before `rb.Range("AA1").Offset(1).Resize(10, 1).Copy`. And I think you should have `.autofilter` before closing the `end with` in your new code. – karma Jul 07 '22 at 12:12

0 Answers0