Ok, I moved the data validation to column Z so you can see the new DV formula in C1.
I've changed the macro to this:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
Dim wsData As Worksheet
Dim wsWasOpen As Boolean: wsWasOpen = True
Dim wbRpt As String: wbRpt = ThisWorkbook.Name
Application.ScreenUpdating = False
Application.EnableEvents = False
If Not Intersect(Target, Range("C1,C2,E2")) Is Nothing Then
'open data wb if needed
On Error Resume Next
Set wsData = Workbooks("PerformanceDataDump.xls").Sheets("Data")
On Error GoTo 0
If wsData Is Nothing Then
Workbooks.Open ("C:\2010\PerformanceDataDump.xls")
Set wsData = Sheets("Data")
ThisWorkbook.Activate
wsWasOpen = False
End If
'import values
Range("A7:G" & Rows.Count).Clear
With wsData
LR = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("AA1") = "Key"
.Range("AA2:AA" & LR).FormulaR1C1 = _
"=AND(RC10='[" & wbRpt & "]Location Report'!R1C3,RC5>='[" & wbRpt & "]Location Report'!R2C3,RC5<='[" & wbRpt & "]Location Report'!R2C5,RC18>0)"
.Range("AA1").AutoFilter
.Range("AA1").AutoFilter Field:=1, Criteria1:="TRUE"
If .Range("A" & .Rows.Count).End(xlUp).Row > 1 Then _
.Range("B2:B" & LR & ",E2:E" & LR & ",I2:I" & LR & ",K2:K" & LR & ",R2:R" & LR & ",T2:T" & LR & ",V2:V" & LR).Copy _
Range("A7")
.AutoFilterMode = False
.Range("AA:AA").Clear
End With
End If
'close the data sheet if it wasn't already open
If Not wsWasOpen Then Workbooks(wsData.Parent.Name).Close False
'cleanup
Set wsData = Nothing
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
This will open the data dump wb if needed. You need only edit the macro to provide the path to where the file is stored.
Bookmarks