Executing a macro

[WLMPilot]

Can a macro automatically be executed by the changing of a cell value?

I have someone who is a master sheet of data. He wants to split this sheet
based on won/lost value in a cell. The data will be moved to a "Won" sheet
or "Lost" sheet based on that indication of won/lost cell value on master
sheet.

I suggested he may need a macro in order to avoid blank lines within the
Sheet(Won) or Sheet(Lost).

Thanks,
Les

 

[Mike H]

Yes a macro can be called by a cell change. The code below executes whenever
a cell in the range A1 - A10 is changed. Right click a sheet tab, view code
paste it in and try it.

Achieveing what you seem to want though would require more detail

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
MsgBox Target.Address & " Changed"
End If
End Sub

Mike

 

[Dave Peterson]

I wouldn't do this.

It can be a royal pain when the user makes a typing mistake.

Instead, I'd do all my data entry and then run a macro that did the work.

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

==========
In fact, I'd do my best not to separate the data. I'd use
data|filter|autofilter to show/hide the stuff I want to see or not.