0

Let me start by apologising in advance for any mistakes in forum etiquette or broken rules, as well as incorrect use of technical terms as I'm still learning slowly.

To summarise, I'm trying to build a sheet that pulls data from a referenced location to a specified cell location based on a drop-down selection made by the user.

Sub retrieve_data()


    Dim LR As Long
    LR = orderLog2.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious).Row
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Sheets("search").UsedRange.Offset(2, 0).ClearContents

    With Sheets("orderLog2")
        .AutoFilterMode = False
        .Range("A2:D" & LR).AutoFilter Field:=1, Criteria1:= _
                                       Sheets("search").Range("C5").Value

        .UsedRange.Offset(0, 0).SpecialCells(xlVisible).Copy
        Sheets("search").Range("E7").PasteSpecial
        .AutoFilterMode = False
    End With

    Application.CutCopyMode = False
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

So in a nutshell, I have a table of data on sheet "orderLog2" (columns A to D) and a drop down list on sheet "search". I was hoping that if a user selects a username from the drop down list (located in cell C5), then automatically all of the relevant data from sheet "orderLog2" will be copied over to sheet "search", cell E7.

Here's the code inserted into sheet "search":

Option Explicit
Private Sub dropdownselection(ByVal Target As Range)
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Address = "$C$5" Then

        Application.EnableEvents = False
        Call retrieve_data
        Application.EnableEvents = True

    End If
End Sub

Lines: " LR = orderLog2.Cells.Find("*", Cells(Rows.Count, Columns.Count), SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row "

is what's causing the run-time error, I assumed that LR is defined correctly and can be used for reference.

If I'm missing any importing info for you guys please feel free to let me know, any help or advice would be massively appreciated.

user3014192
  • 3
  • 1
  • 3
  • 1
    You dont properly reference the sheets you are working with. Every `.Cell` should be preceded by the relvant worksheet. – Luuklag Jul 11 '19 at 11:30
  • Possible duplicate of [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Luuklag Jul 11 '19 at 11:32
  • Hi Luuklag, thanks for the advice I'll make sure to make a note for future reference and thanks for the link to the similar thread. – user3014192 Jul 11 '19 at 11:42

1 Answers1

0

Try:

Sub retrieve_data()

Dim orderlog2 As Worksheet, LR As Long
Set orderlog2 = Sheets("orderlog2")

With orderlog2
    LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End with

'Rest of code...

Maybe you don't even need that last row:

Sub Test()

Dim orderlog2 As Worksheet, LR As Long
Set orderlog2 = Sheets("orderlog2")

Application.ScreenUpdating = False
Application.EnableEvents = False

With Intersect(orderlog2.UsedRange, orderlog2.Columns("A:C"))
    .AutoFilter 1, Sheets("search").Range("C5").Value
    .Offset(1).Resize(.Rows.Count - 1).Copy Sheets("search").Range("E7")
    .AutoFilter
End With

'Rest of code...
JvdV
  • 70,606
  • 8
  • 39
  • 70
  • Hi JvdV, your changes work many thanks! There is a peculiar outcome now though, if I choose 'employee 1' from the drop down (and if employee 1 has say 50 rows of data in the source table) it fills the destination location with 50 rows but with data from employee's 2 and 3? – user3014192 Jul 11 '19 at 11:44
  • I updated the answer, I don't know what it is you really want, I read until the line that troubled you and I have now updated with a possible shorter version to achieve the same thing. – JvdV Jul 11 '19 at 12:05