Create list of Find Values (Excel 2000>)

Hi,
I had to generate a list of cars on a new sheet based on finding them on multiple sheets. I found a great bit of sample code in the help files of VBA which i was able to use with great effect in my macro. The loop structure:
<pre> Set CT = .Find(CarType, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
If Not CT Is Nothing Then
FirstAddress = CT.Address
Do
i = i + 1
CT.EntireRow.Copy Sheets(1).Range("A" & i)
Set CT = .FindNext(CT)
Loop While Not CT Is Nothing And CT.Address <> FirstAddress
</pre>

assigns a cell reference to the found value, and this you can use to paste, or (anything else) you need to do with a range! I guess this could also help with a recent <post#=484580>post 484580</post#> I read?

The macro in the sample workbook works well, but if any VBA guru's have the time...can you please advise me if the code is good, or if any further structural/useful improvements can be made! I am ALWAYS willing to learn more and be advised on improvements!

Re: Create list of Find Values (Excel 2000>)

Most of the code is OK, but the line

.Value = "List of " & CarType & "'s from " & Sh & " Country Sheets!"

will report the wrong number of sheets:
- At the end of the For .. Next loop, Sh will be the number of sheets + 1
- The newly inserted sheet shouldn't be counted, you actually want the number of sheets - 1.

Re: Create list of Find Values (Excel 2000>)

Thanx for that. I made the adjustment on the Belgium sheet! However, the sheet count will not work by saying (Sh - 2). I plan to run this procedure for say; 3 or 5 different cars at any point, and then the (-2) must increment each time within the WBook! This is an interesting problem you picked up! (Strange that I didn't even note that!) What might be done here is to count the sheets starting at Australia, or create a static value (although i don't think that will work!) Any ideas to get around this? I cant say Sheets.count(Australia:Sheets(xlEnd).count)????

Re: Create list of Find Values (Excel 2000>)

Its true what you suggest! I initialy thought it would be good to have the results on a seperate sheet, but in retrospect, it seems to be good to have the results on one sheet anyways!

I will play around with the idea! Tx

PS: Just thinking...if I do run it one one sheet, I may need to recode that it must paste the next car type below the previous result. So I will need to locate the next blank line below the data. I will use Range("A65536").xlUp.Select.