How to paste values by row in loop?

I have patched together the following code and it almost does what I need. In short, I need to find any row that contains data for a person and paste the whole row to an output spreadsheet. The remaining problem I have is in the comments for the last subroutine. I want to paste both the Format (date, number, money, text) and the Values into the target spreadsheet but what I have now produces #REF! for any formula referencing another spreadsheet.

I patched together the following tasks from forums around the net:
1 - Load a tag file list of names into an array
2 - Pull all files in all subdirectories to be searched sequentially/recursively (since my files don't have multiple worksheets, I didn't try to include that loop)
3 - Select rows that contain names of interest
4 - Paste the rows to an output sheet accompanied by identifying filename and tag reference

The last task also needs a pastevalues result but I can't figure out a syntax that will work with rows inside a loop.

Since this program loops through (many files X many rows X many names), it will take many hours to complete. Any suggestions to increase efficiency or correct bad programming methods are welcome. I know some of my code is kludgey but that's what my random stumbling forced to work so far. I have a similar VBS that does this task for text files but VBA seems a little slower in simple two-file tests so far and all this work will be for nothing if it needs more than overnight to produce results.

The Following User Says Thank You to sdckapr For This Useful Post:

Ok, that did it. I was not aware of one-line and two-line format for copy and paste. The error messages implied to me that the problem was that PasteValues did not work with Rows and I could not quite get Range and Cells to work.

Now I just need to work on efficiency and some kind of progress display.

Just to elaborate: The copy / paste(special) in 2 lines is generic:
Range1.copy
Range2.paste

or
Range1.copy
Range2.pastespecial Paste:xlPasteValues

With copy/paste, it can be combined:
Range1.copy Range2

The pastespecial can not be combined into the copy command, it has no options for this.

As for efficiency, doing it row by row is probably the most inefficient. Doing one copy and one paste operation would be the most effective. It may require some sorting to combine them together. As to a progress display look at the thread http://windowssecrets.com/forums/sho...usbar+progress

Unfortunately, the spreadsheets I am searching are not all in the same format so I do not know which column the names or email addresses will be in.

I thought about searching each worksheet by each name instead of searching each row by name but unless the internal workings of the Find command are much more efficient than VBA loops, I thought that would be worse. Maybe it's worth a a test. Or maybe I should think about searching in columns since there are fewer of them?

Unless I go through the subdirectories first for a file count I don't know a percentage complete. I think I would rather see a display of how long the program has been running with the number of files processed, the number of lines output, and the currently processing file name.

I didn't see a simple text progress display with a google search so I may just use what I already have code for.

I used CreateObject("InternetExplorer.Application") for display in my VBS program. I think I will just re-use that code. However it does seem to dangerously connect Explorer and Excel. I had to reboot once while testing the code because Excel and Explorer locked up.

Going for an increase in efficiency, do you think the following is possible and better than a row by row search?

open a spreadsheet
loop on names
findall for the email address = range1
findall for the last name = range2
get rows for range2 = range3
findall in range3 for first name = range4
add range1 and range4 = range5
get rows for range5 = range6
copy and paste range6 to the output spreadsheet
loop on names