I have one sheet called master list which is basically a customer list of:

custid, name, address, state, ...
On a different sheet I want to make a report that does something like:

State: <enter state
(Macro, or auto) Select * from master_list where $State_Col = state

This way I can have a nice formatted report for each state, and I can
simply enter the state and either run a macro, or have it auto run based
upon a change in state to make the rest of the report.

I know this is probably best suited for a database, but for right now we
really like the auto-filter capability and dont want to move everything
to a DB.

Peter

Why not use Autofilter, then, selecting the State from your State
column? You can highlight the visible cells and copy them to another
sheet quite easily.

Hope this helps.

Pete

Because I want to avoid a copy and paste. I need to automate it just a
little bit. So I have this function

Sub DoReport()
With Worksheets("Master List")
.Range("A2:O2").Copy Destination:=Worksheets("Report").Range("A4")
End With

End Sub

But I would like to modify that so it paste's only values (no formating,
like paste special) and so that it only selects where state = $Report$A1

Peter

Pete_UK wrote:
Why not use Autofilter, then, selecting the State from your State
column? You can highlight the visible cells and copy them to another
sheet quite easily.

Hope this helps.

Pete