I went through your code, and assumed that you have two different
worksheets:

1. The first worksheet (let's name it "Sheet1") contains the cell
(E10) which you want to check its value.

2. The second worksheet ("QBQuery1_1Criteria") contains the ranges
you are going to copy from and paste into.


Put this code  in worksheet (Sheet1) :

-----------------------------------------------------------
Private Sub Worksheet_Change(ByVal target  As Range)
If Target.Address = "$E$10" Then
With Worksheets("QBQuery1_1Criteria")
Select Case Target
Case "N/A"
.Range("O1:O530").Copy .Range("K1")
Case "All Projects Actual"
.Range("P1:P530").Copy .Range("K1")
End Select
End With
End If
End Sub
-----------------------------------------------------------


Here are my comments:

1. Change the event to (Worksheet_Change).

2. Target.Address will give the address of the changed cell, while
Target will give its value.

3. Reduce your code by using (SourceRange.Copy DestinationRange).