Searching multiple tabs for value and putting them in a list

[0nyx1756]

Hi there guys,

I’ve got five spreadsheets which cover purchases of various items and whether the person who bought the item has paid for it yet,

Sheet 1 (Cars)

Staff member Paid in full?
Bob Yes
Sid Yes
James Yes
Edward No
Ash No

Sheet 2 (boats)

Staff member Paid in full?
Bob Yes
Ash Yes
James Yes
Edward Yes

Sheet 3 (houses)

Staff member Paid in full?
Bob No
Ash Yes
James Yes
Edward No
John Yes

Sheet 4 (shops)

Staff member Paid in full?
Bob Yes
Ash Yes
Chris Yes
Steve No
John Yes

Sheet 5 (shares)

Staff member Paid in full?
Bob Yes
Ash No
Chris No
Steve Yes
John Yes

What I’d like to do is look across all five sheets and produce a list of all people who have outstanding debt (anyone who has a "No" in their 'paid in full' column), put this information in sheet 6 and remove any duplicates.

I thought it would be best to use VBA, it’s going to be something that’s done quite regularly so I felt a macro would be most appropriate. Can anyone help? It should be quite simple i would have thought but i'm having no luck.

[0nyx1756]

here is the sheet

[tigeravatar]

0nyx1756,

Give this a try:

Please Login or Register  to view this content.

[0nyx1756]

That seems to have the basics down however it produces the following error

Run-Time error ‘91’:

Object variable or With block variable not yet

It also doesn’t remove duplicates (if Bob has not paid for his car and his house, he will appear on the list twice) and is there any way to put it into an existing sheet rather than a new one?

[tigeravatar]

When I ran the code I did not get any error and it did remove duplicates. What line is highlighted when you get the error (click Debug)? What is the sheet name you would like the results in and where in that sheet would you like them?

[0nyx175]

When I ran the code I did not get any error and it did remove duplicates. What line is highlighted when you get the error (click Debug)? What is the sheet name you would like the results in and where in that sheet would you like them?

I think it came up with the error because i ran it twice, when i ran it the first time it came up fine. I'd like the results to be produced in Sheet 6 in column A.

[tigeravatar]

0nyx175,

Updated code. Just change wsDestName as needed. Tested to ensure it can be run multiple times in a row with no error and that it only returns unique values:

Please Login or Register  to view this content.