How do you copy all data in a row only if value in column A is "xy

[kookie]

Hi,

i need help. I'm doing a sales spreadsheet for each salesperson. And i would
like to copy all data from sheet 1 (main sales sheet) to sheet 2 (particular
person) if value on column A is equal to "david". I found the same problem
but link is not available anymore. Can anyone help me please?

You will be very much appreciated.


Thanks,

-kookie

 

[Ron de Bruin]

Hi kookie

See this page for code to do this
See the macro example: Create a new sheet for all unique values
http://www.rondebruin.nl/copy5.htm

This is the manual way

Select a cell in your data table and use Data>Filter>AutoFilter to activate AutoFilter.
In Excel 2007 click on the Data tab on the ribbon and then on Filter in the Sort & Filter group
Tip: Shortcut for every English Excel version is Alt d f f

In each header cell a dropdown will appear next to your field name.
Click on the dropdown in the Name field and choose david.

Copy the filter result to a new worksheet:

1) Select the whole data range or if you not have empty rows/columns in the range
you can use Ctrl * to select all data or use F5>Special>Current region>OK with
one cell selected in the data range.
If it copy all the data (there are a few reports of 2007 users) use also the shortcut
Alt ; or F5>Special>Visible cells only>OK to select only the Visible cells.

2) Ctrl c in all Excel versions or:
Excel 97-2003: Edit>Copy
Excel 2007: Home tab>Clipboard group>Copy

3) Shift F11 in all Excel versions to insert a worksheet or:
Excel 97-2003: Insert>Worksheet
Excel 2007: Home tab>Cells group>Insert...Sheet
Or use button next to the last tab

4) Ctrl v in all Excel versions or:
Excel 97-2003: Edit>Paste
Excel 2007: Home tab>Clipboard group>Paste

5) Select the worksheet with the filter

6) Press Esc

7) Press Alt d f f in every English Excel version or:
Excel 97-2003: Data>Filter>AutoFilter to turn off AutoFilter.
Excel 2007: Data tab>Sort & Filter>Filter