Transfer Subset of Data to Separate Worksheet

[Ghost Rider]

As a newcomer to the forum I'm sure the answer to my question is ther
somewhere but I've no idea where to look! Therefore can somebod
please point me in the right direction or provide a solution to m
problem.

I have a multi-sheet Excel file that has a single worksheet that hold
all the basic data. The other sheets use a variety of formulas t
filter/query this data to display tables and charts. The main sheet i
currently 1700+ rows long and between 5 and 10 rows are added daily.
One of the columns in this worksheet contains a category value such a
Aeroplane, Car, Bicycle etc which is populated from a list a validatio
values. What I want to be able to do is automaically pass a filtere
set of data e.g. just those rows that contain Aeroplane to anothe
worksheet.

Therefore my question is how do I automatically populate a separat
worksheet with a subset of data from a growing table of rows

 

[Jimbola]

I think the way to do this would be to use the Advanced Filter option in Excel.
Basically in the worksheet where you want the sub set you would have a the
headings of the main worksheet at the top with the criteria for that work
sheet e.g.

Transport Type Cost
Car (each worksheet
would have a
different vehicle)

 

[Jimbola]

Whoops...wrong button.....as I was saying....

I think the way to do this would be to use the Advanced Filter option in
Excel.
Basically in the worksheet where you want the sub set you would have a the
headings of the main worksheet at the top with the criteria for that work
sheet e.g.

Transport Type Cost
Car (each worksheet
would have a
different
vehicle)

You then click Data, Filter, Advanced Filter.
Slect "Copy to another location"
As list range select the main list, ensure you select additional rows as the
list expands.
As criteria range select the header and criteria in the subset sheet. I.e.
what I am taling about above.
In copy to, select where you want the data to appear.

If the list continually increases record the above as a macro and create a
button.
Or attach the code to the worksheet activate event, so the list refreshes
everytime the worksheet is selected.

Hope that helps

 

[Alan Beban]

As a newcomer to the forum I'm sure the answer to my question is there
somewhere but I've no idea where to look! Therefore can somebody
please point me in the right direction or provide a solution to my
problem.

I have a multi-sheet Excel file that has a single worksheet that holds
all the basic data. The other sheets use a variety of formulas to
filter/query this data to display tables and charts. The main sheet is
currently 1700+ rows long and between 5 and 10 rows are added daily.
One of the columns in this worksheet contains a category value such as
Aeroplane, Car, Bicycle etc which is populated from a list a validation
values. What I want to be able to do is automaically pass a filtered
set of data e.g. just those rows that contain Aeroplane to another
worksheet.

Therefore my question is how do I automatically populate a separate
worksheet with a subset of data from a growing table of rows?

The following VBA code, to be inserted in a general module in your
workbook, assumes your worksheet with all the basic data is named
"BasicSheet", the data is in A1:J3000 with the first row holding the
column headers, Column I is holding Aeroplane, Car etc., the sheet to
which the data is to be copied is named "CopyToSheet", and the copied
data is start at Cell A1. You would execute it by running Sub abcde2;
when the input box appears, type Aeroplane, Car or whatever.

Sub abcde(a)
Dim wsBase As Worksheet, wsCopy As Worksheet
Set wsBase = Sheets("BasicSheet")
Set wsCopy = Sheets("CopyToSheet")
Application.ScreenUpdating = False
wsCopy.Cells.ClearContents
wsBase.Range("A1:J3000").AutoFilter Field:=9, Criteria1:=a
wsBase.Range("A2:J2000").Copy wsCopy.Range("A1")
Application.CutCopyMode = False
wsBase.Range("A1:J2000").AutoFilter
Application.ScreenUpdating = True
End Sub
Sub abcde2()
Dim b
b = InputBox("Filter Criterion")
abcde b
End Sub

Alan Beban

 

[Gord Dibben]

Ghost

Have a look at some of Ron de Bruin's VBA routines for copying filtered
subsets of data to a new worksheet.

http://www.rondebruin.nl/copy5.htm

Gord Dibben Excel MVP

 

[Alan Beban]

There's really no reason to use two Sub procedures; I don't know what I
was thinking:

Sub abcde()
Dim a
Dim wsBase As Worksheet, wsCopy As Worksheet
Dim rngBase As Range

Set wsBase = Sheets("BasicSheet")
Set rngBase = wsBase.Range("A1:J3000")
Set wsCopy = Sheets("CopyToSheet")

Application.ScreenUpdating = False
wsCopy.Cells.ClearContents
a = InputBox("Filter Criterion")

rngBase.AutoFilter Field:=9, Criteria1:=a
wsBase.Range("A2:J3000").Copy wsCopy.Range("A1")
Application.CutCopyMode = False
rngBase.AutoFilter

Application.ScreenUpdating = True
End Sub

Alan Beban