What i have to do is take the list from "data" and add it (filtered) to
"look_up". I will then be looking at "data2", "data3" and "data4" and
doing
the same with them but adding them below the values from "data" so I get a
continuous list from the 4 worksheets in a column. I will then filter that
list and write the definative list to "summary".
I'll actually be doing this with contract numbers at work (and not dog,
cog
or bed) and then importing the total spend, estimate, budget and forecast
hours next to each contract to show any discrepencies that need to be
addressed. As some contracts are in the future i'll get contract numbers
on
the "estimate" sheet that are not on the "spend" sheet. As new data is
being
added on a monthly basis I don't want to miss anything so thought creating
a
summary sheet via VB or a Macro pull out the information faster (and be a
lot less prone to mistakes).
Regards
Ric
Got the advanced filter working, thanks. Here is the
VB for the 4 pages
' This looks at the Spend worksheet and retrieves the contract numbers
Range("A1:A2").Select
Sheets("spend").Range("B2:B500").AdvancedFilter
Action:=xlFilterCopy, _
CopyToRange:=Range("A2"), Unique:=True
' This looks at the Estimate worksheet and retrieves the contract
numbers
Range("B1:B2").Select
Sheets("estimate").Range("B2:B500").AdvancedFilter
Action:=xlFilterCopy,
_
CopyToRange:=Range("B2"), Unique:=True
' This looks at the Directive worksheet and retrieves the contract
numbers
Range("C1:C2").Select
Sheets("dir").Range("B2:B500").AdvancedFilter Action:=xlFilterCopy,
_
CopyToRange:=Range("C2"), Unique:=True
' This looks at the RPS worksheet and retrieves the contract numbers
Range("D1:D2").Select
Sheets("rps").Range("B2:B500").AdvancedFilter Action:=xlFilterCopy,
_
CopyToRange:=Range("D2"), Unique:=True
Currently i've just told it to write the results in sperate columns but
I would like it to find the end of the last data and add it to the end so
it gives a continuos list. Also i've specified a range ("B2:B500") but I
would like it to look for a blank cell and stop instead of specifying the
range incase I get more records than 500.
Regards
Ric