Thread
:
Use VBA to display record with criteria
View Single Post
#
9
Posted to microsoft.public.excel.programming
Learn-more
external usenet poster
Posts: 25
Use VBA to display record with criteria
Hi Don,
Finally got it work but need to have Row1 of Sheet1 (date) blank or put in a
heading, it will always display in Sheet2 or even mess up the display. Not
sure why.
Thanks a lot.
"Don Guillett" wrote:
A B C
D
apple one 25
Apple one Mon 1
Apple one Thu 6
Apple one Sat 8
Apple one Mon 10
I set up the destination sheet as above with criteria on row 2. Assigned the
macro to a shape and fired from there. If all else fails, send your workbook
to my address below.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Learn-more" wrote in message
...
Hi Don,
I think that's what I want, however, as my VBA knowledge is still limited,
as I run the code, it come back with Run-time error "9", Subscript out of
range. I print out the help and tried a few things but still not get it
right.
Any idea why and how to fix it?
Thanks for helping.
"Don Guillett" wrote:
Data on sheet 1. Sheet 2 has headers on row 1 and criteria on row 2. Fire
from sheet 2
sub getdatatoothesheet()
With Sheets("sheet1")
lr = .Cells(Rows.Count, 1).End(xlUp).Row
.Range("a1:d" & lr).AutoFilter Field:=1, Criteria1:=Range("a2") '
"Apple"
.Range("a1:d" & lr).AutoFilter Field:=2, Criteria1:=Range("b2") '
"One"
.Range("a2:d" & lr).Copy Sheets("sheet2").Range("a3")
.Range("a1:d" & lr).AutoFilter
End With
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Learn-more" wrote in message
...
I know only a little about using VBA and now have a problem not sure how
to
work it out. I need to display some records in a new sheet according to
some
criteria,
Example in Sheet A
A B C D
1 Apple one Mon 1
2 Banana two Tue 2
3 Apple two Wed 5
4 Apple one Thu 6
Say I need to display in Sheet B the records that satisfy A=Apple and
B=one
and also with a total for D
I know how to use ComboBox etc to crate the list but dont know how to
make
the selection and move to a new sheet.
Can anyone tell me how or show me some web site that I can find some
examples.
Thanks so much.
Reply With Quote
Learn-more
View Public Profile
Find all posts by Learn-more