Running a procedure again

[GK039]

Hi everyone,

I have a sheet called Data and a second sheet called Report where i want to extract filtered results from Data Sheet. In Report Sheet Cell C3 houses a validation list with branch names of my company. Using the following vb solution i can produce the report based on the value of C3. What my problem is, is that i want the report to be reproduced if someone changes the selection to cell C3. At the moment I have passed the procedure to a command button and I have to use a second procedure where i use clearcontents to clear the result region. And then I run the first procedure again.

Here is the code:

Sub results()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Integer
Dim j As Integer
Dim lrow As Integer

j = 7
Application.ScreenUpdating = False

Set ws1 = Worksheets("data")
Set ws2 = Worksheets("report")
lrow = ws1.Range("a3").End(xlDown).Row

ws2.Activate

Range("c" & j).Select

For i = 1 To lrow
If ws1.Range("e" & i).Value = ws2.Range("c3").Value Then
Union(ws1.Cells(i, "e"), ws1.Cells(i, "a"), ws1.Cells(i, "i"), ws1.Cells(i, "j"), ws1.Cells(i, "g"), ws1.Cells(i, "c")).Copy Destination:=ws2.Range("c" & j)
End If
If IsEmpty(Range("c" & j)) Then
j = j
Else
j = j + 1
End If
Next i


Application.ScreenUpdating = True


End Sub

Variable j equals to 7 as Row7 in Report sheet is the starting row for the report. Row 6 are the headings

I'd appreciate any help

Thanks in advance

George

 

[Smitty]

You can use a change event to call the code when C3 changes:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("C3")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[GK039]

Hi Smitty,

Thanks a lot for your help. It works perfectly!

George

You can use a change event to call the code when C3 changes:

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("C3")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here...)

End Sub

HTH,