Simple oversight on my part in this code I'm sure

[pglufkin]

In my mind, the following code is simple. It waits to see what is chosen in
cell E10 of two possible choices through data validation, and then
run a procedure based on what is chosen.

Cell E10 is one sheet within the same workbook and the ranges to be copied
are in another sheet in the same workbook. The code resides in the sheet
where E10 is.

Problem is, when I choose something in E10, nothing happens. Can
anyone help me?

Private Sub Workbook_SheetSelectionChange(ByVal Sheet As Object, ByVal
Target As Excel.Range)
If Target = "E10" Then
Select Case Range("E10").Value
Case "N/A"
Range("QBQuery1_1Criteria!O1:O530").Select
Selection.Copy
Range("QBQuery1_1Criteria!K1").Select
ActiveSheet.Paste
Case "All Projects Actual"
Range("QBQuery1_1Criteria!P1530").Select
Selection.Copy
Range("QBQuery1_1Criteria!K1").Select
ActiveSheet.Paste
End Select
End If
End Sub

 

[Tom Ogilvy]

SelectionChange fires when you select the cell - so the value hasn't
changed. Try using the change event.

Target will probably never have a value of "E10". I suspect you want to
check the address of the changed cell.

Private Sub Workbook_SheetChange(ByVal Sheet As Object, _
ByVal Target As Excel.Range)
If Target.address = "$E$10" Then
Select Case Target.Value
Case "N/A"
Range("QBQuery1_1Criteria!O1:O530").Select
Selection.Copy
Range("QBQuery1_1Criteria!K1").Select
ActiveSheet.Paste
Case "All Projects Actual"
Range("QBQuery1_1Criteria!P1530").Select
Selection.Copy
Range("QBQuery1_1Criteria!K1").Select
ActiveSheet.Paste
End Select
End If
End Sub