Steve,
Then how about a macro? After filtering the sheet "All Data" for the desired values, this macro
will copy the visible cells to the sheet "Selected Data" (after clearing it):
Sub TransferAllData()
Worksheets("Selected Data").UsedRange.ClearContents
Worksheets("All Data").Range("A1").CurrentRegion.SpecialCells(xlCe llTypeVisible).Copy _
Worksheets("Selected Data").Range("A1")
End Sub
Or, if you know which columns you want: (I hope you can see the pattern....)
Sub TransferSomeData()
Worksheets("Selected Data").UsedRange.ClearContents
Worksheets("All Data").Range("A:A").SpecialCells(xlCellTypeVisible ).Copy _
Worksheets("Selected Data").Range("A:A")
Worksheets("All Data").Range("D:D").SpecialCells(xlCellTypeVisible ).Copy _
Worksheets("Selected Data").Range("B:B")
Worksheets("All Data").Range("L:L").SpecialCells(xlCellTypeVisible ).Copy _
Worksheets("Selected Data").Range("C:C")
End Sub
HTH,
Bernie
MS Excel MVP
"ChemicalJasper" wrote in message
ups.com...
On May 15, 3:18 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Steve,
The best thing to do is to use a data filter on column M of the first sheet, and select the value
from the drop down at the top of that column. That way, you won't need to use a third sheet at
all.
HTH,
Bernie
MS Excel MVP
"ChemicalJasper" wrote in message
oups.com...
Dear Learned Users,
I have a problem with data sorting, please can anyone advise on the
best function to use?
I have a sheet with some 5500 rows of data (across 40+ colums) of
which I cannot alter the format.
I have a second sheet with some 150 rows of data
On a third sheet I have a combo box using the second sheet as the
list. I want to be able to select a record from the list (from the
second sheet) and populate a report/list of records from the first
sheet which match the data selected from the drop down.
The data in the first sheet that may match is in column 'M' and I need
to populate the report sheet with data from 'B' to 'AT' of the first
sheet.
Is there a data function within Excel that can do this or will it have
to be VB coded?
Thanking you
Steve- Hide quoted text -
- Show quoted text -
Thanks for the sugestion Bernie,
I already have filters on the first sheet, however I want to be able
to pull the data out (selected data colums) into a third report sheet
and then be able to do further manupilation on the data with out
affecting the primary records.
Thanking you
Steve