New to MS Excel VBA

[Monkeydreams]

I'm a systems guy who is doing an inventory of tools being used at a
site I am currently contracting at. I've divided my Excel spreadsheet
into 4 worksheets representing the platforms that these tools run on,
e.g. Unix, windows, etc. I'm using an advanced filter on each worksheet
and through my macro/vba code, ask the user to enter a vendor's name.
This name is then plugged into the criteria range on each work sheet so
that the user will only see tools by that vendor.

My next task is to consolidate that vendor's tools into a fifth
worksheet by copying and pasting from each individual platform
worksheet. My problem arises when, using the offset method or property,
the copy-paste operation picks up rows that are filtered out. In other
words, I look for an empty cell in the Vendor Name column as I move
down through the filtered rows signalling to go to the next worksheet
and extract the vendor's tools from there and so on through all the
worksheets. So my question is how to avoid the hidden rows once through
the rows I'm filtering for.

 

[TC]

Surely a job for a database program (eg. Access) - not a spreadsheet?

HTH,
TC

 

[Monkeydreams]

I agree but was wondering whether what I was asking was doable with
Excel

 

[TC]

Sure, you could do it in Excel - but Excel doesn't sound like the right
tool, for what you have described, IMHO. It sounds much more like a
typical database application.

In a database, eg. Access, you'd have, say, a table for Vendors, one
for Tools, and another for Platforms. Then you'd probably need some
joining tables, to define the neccessary many-to-many relationships.
Then you'd just write queries, and perhaps some reports, to show the
data however you wanted.

If you don't have any database experience, and the work isn't too
urgent, maybe you could use this opportunity to try one out

HTH,
TC

 

[mailto.jos]

It might be too simple to be true, but what about a simple
If-statement?
Once you know which vendor the user has filtered out, you can just
browse through all the rows (including the hidden ones) and check if
the cell contains the vendor's name. If so, include in sheet 5, if
not, move on to next row.

I know this isn't an actual answer to your question (i'm actually
curious about that too, since it might come in handy) but it provides a
workaround.

Cheers!

Monkeydreams schreef: