My question is concerning Visual Basic (VB) and Excel. I want to find/copy data from one sheet to another.?

Question

Simply put, I want to find and copy information from one sheet to another in Excel using Visual Basic. We have a large Excel database spanning 10-12 Sheets.

On the first sheet, we have two search criteria, say Ford and Mustang. I want to search the other 12 Worksheets and return every Row that has Ford or Mustang in it. The information will go in Worksheet A. The information will go in each successive rom, ala it won't be overwriting the data it found.

Thanks!

Simply put, I want to find and copy information from one sheet to another in Excel using Visual Basic. We have a large Excel database spanning 10-12 Sheets.

On the first sheet, we have two search criteria, say Ford and Mustang. I want to search the other 12 Worksheets and return every Row that has Ford or Mustang in it. The information will go in Worksheet A. The information will go in each successive rom, ala it won't be overwriting the data it found.

More Information: There is a strong desire to automate this process. Many users will be conducting many DIFFERENT searches of this information. Rather them thumb through all 12 pages, we wanted to simplify. Maybe making it easier, "Ford" or "Mustang" (or whatever search Text)would be the only entry in a cell. Thus, you will not have to search through other text in the cell.

Thanks!

Answer 1

Copy and paste silly!

Answer 2

Since you are dealing with vehicles I can assume your company is using Office Professional. Open Access, create a database, import all the data. Before that, you should consolidate all the data into one sheet. If the rows are too many ie >65000 rows, then you import the data one sheet at a time.
You now have the complete list in Access. Using simple query, extract out all the Ford and Mustangs. Export it back to Excel. The steps are simpler.

Answer 3

I can't recommend using a macro for this exercise as you have described it. That's too much work. You can accomplish the same with simple filtering.

Use the default filtering. Click Data, Filter, Autofilter.

With only a dozen sheets to copy, it will be quick and easy to copy the appropriate rows.

Hope this helps!
Kind Regards,
QwertyKPH @ Yahoo!

Answer 4

You can rather using the Copy and paste in Vblike this:
Sheet1.Range( "A1").Copy
Sheet2.Range( "B1").Paste
You can do this
Sheet2.Range( "B1").Value = Sheet1.Range( "A1").Value
Which is the same as:
Worksheets( "Sheet1").Range( "B1").Value = Worksheets( "Sheet1").Range( "A1").Value

Enjoy my profile, Yes, I am the VBAXLMan