I have 14 workbooks, each with a range of data consisting of 5 columns and
around 500 rows. Each range consists of peoples names (approx 30 in each) and
I need to break each range into sub ranges of people onto separate tabs (very
big job as you can imagine) I have made a start by highlighting the main
range filtering on a person and copying to another tab, then filter on
another person (making my way down the list) and copying to a different tab
and so on.
I need a piece of code that would automatically select/filter on each person
and if at all possible to create a new tab and name the tab the persons name
( A very tall order I think)
Any help greatly appreciated
Regards
John

I'd look at these techniques:

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

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

JohnUK wrote:

I have 14 workbooks, each with a range of data consisting of 5 columns and
around 500 rows. Each range consists of peoples names (approx 30 in each) and
I need to break each range into sub ranges of people onto separate tabs (very
big job as you can imagine) I have made a start by highlighting the main
range filtering on a person and copying to another tab, then filter on
another person (making my way down the list) and copying to a different tab
and so on.
I need a piece of code that would automatically select/filter on each person
and if at all possible to create a new tab and name the tab the persons name
( A very tall order I think)
Any help greatly appreciated
Regards
John

--

Dave Peterson

Many thanks Dave, this is exactly what I wanted, under "Create New Sheets
from Filtered List" from contextures.com
Regards
John

"Dave Peterson" wrote:

I'd look at these techniques:

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

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

JohnUK wrote:

I have 14 workbooks, each with a range of data consisting of 5 columns and
around 500 rows. Each range consists of peoples names (approx 30 in each) and
I need to break each range into sub ranges of people onto separate tabs (very
big job as you can imagine) I have made a start by highlighting the main
range filtering on a person and copying to another tab, then filter on
another person (making my way down the list) and copying to a different tab
and so on.
I need a piece of code that would automatically select/filter on each person
and if at all possible to create a new tab and name the tab the persons name
( A very tall order I think)
Any help greatly appreciated
Regards
John

--

Dave Peterson