Code for searching multiple worksheets and copying matching data to 1 w/sheet

**lambi15** · 03-08-2013, 05:39 AM

Hi there,

I have a workbook with various worksheets. On specific worksheets there is a table where I add issues with equipment, with the date issue arose and a rectified date when resolved. I now have a search worksheet where i want to find any un-rectified issues across all the tables on the different worksheets. The code i have written so far finds un-rectified issues on one worksheet and brings it to the search sheet. My problem is how to make it loop through the other worksheets and do the same check.

Here is my code so far;

Sub search_button1_click()

Dim r As Integer

'Finds last row of data

r = 30000

Do Until Sheet11.Cells(r, 4).Value <> ""
r = r - 1

Loop

r = r + 1

' Puts data onto sheet

Sheet48.Cells(r, 1) = Sheet11.Cells(r, 1).Value
Sheet48.Cells(r, 2) = Sheet11.Cells(r, 2).Value
Sheet48.Cells(r, 3) = Sheet11.Cells(r, 3).Value
Sheet48.Cells(r, 4) = Sheet11.Cells(r, 4).Value

End Sub

**AB33** · 03-08-2013, 06:06 AM

Lambi,
Please wrap your code with code tags.
If you could attach a sample, I will try to write you a different code. Your current code requires lots of adjustments, we have to write it as if from the start.
To attach a sample excel book. Go to advance, half way the page you see an attachment.

**lambi15** · 03-08-2013, 06:13 AM

Hi AB33,

I have attached the whole workbook for you to see for yourself. You will see that there are various worksheets that have the issue table on and others which list the equipment. The search worksheet is the one where if I was to click the CMDbutton it would complete the search of all the tables.

Thank you for your help

Lambi

**AB33** · 03-08-2013, 06:38 AM

Lambi,

"The code i have written so far finds un-rectified issues"
I need to know what you are searching for. Are you searching for a word, or value? I know you wanted to search across sheets, in all cells, but I need to know what to search. In your code, you have indicated columns D of search sheet, but column D is empty.

**lambi15** · 03-08-2013, 06:43 AM

Hi AB33,

The search wants to find any issues that have been logged but haven't yet been rectified(no date in column 4), the search checks each table and then if the row has data and column 4 is empty it shows that as a result on the search page.

**lambi15** · 03-08-2013, 06:45 AM

Hi AB33,

The search wants to find any issues that have been logged but haven't yet been rectified(no date in column 4), the search checks each table and then if the row has data and column 4 is empty it shows that as a result on the search page.

**AB33** · 03-08-2013, 07:07 AM

I think my understanding is you want to search for any data that has been logged-on. I have added few lines to make your code to loop through each sheet, but I do not know how to search for empty value. There was a similar thread in this formum which deals with loggin code and if you are interested, I can send you the link.

Please Login or Register  to view this content.

**mike7952** · 03-08-2013, 09:30 AM

Maybe this

Please Login or Register  to view this content.

**HaHoBe** · 03-08-2013, 09:34 AM

Hi, AB33,

your code loops through the sheets but will always copy from the sheet with Codename Sheet11, and there is not splitting between the search sheet and the sheet on which to loop through.

@lambi15:
I´d recommend to add a column at Column A showing the name of the worksheet where the issue is to be found (which is missing in this piece of code):

Please Login or Register  to view this content.

Ciao,
Holger

**AB33** · 03-08-2013, 09:42 AM

Hi Holger,
It was not my code, but rather OP's one. I only added 2-3 lines to OP's lines.

**lambi15** · 03-08-2013, 09:44 AM

Hi Everyone,

Thank you for your help, it is now working thanks to Holger's code.

**GazzaLDN** · 03-08-2013, 09:56 AM

Originally Posted by AB33

I think my understanding is you want to search for any data that has been logged-on. I have added few lines to make your code to loop through each sheet, but I do not know how to search for empty value. There was a similar thread in this formum which deals with loggin code and if you are interested, I can send you the link.

Please Login or Register  to view this content.

Would = vbNullString be better than <> "" ?

**AB33** · 03-08-2013, 10:03 PM

GazzaDn,
Yes, you are right!
"" could include a cell which appears to be empty to our eyes because a formula could return nil but in excel, it is not empty. So, to be absolutley sure you should use the isempty function to test if a cell is indeed empty.

**mike7952** · 03-08-2013, 10:14 PM

See below. Both vbNullString and ="" work the same.

Please Login or Register  to view this content.

**AB33** · 03-08-2013, 10:32 PM

Mike,
"" is not the same as empty. I have read in a VBA written by three guys(Still have got that book) whose advice was "" is not always empty, it could include a cell which returns what looks empty but it is not.

**GazzaLDN** · 03-09-2013, 07:55 AM

I could see a scenario where a task takes multiple days and someone uses a formula to return "" as the answer until complete.

The above would find the Value "" and write it to the summary as un-rectified and a duplicate work order could be raised. A little far fetched maybe, but these things can happen when the usual person doing the task is on Holiday.

Perhaps its a subtle difference between un-rectified and un-addressed.

**GazzaLDN** · 03-09-2013, 07:58 AM

double post

Code for searching multiple worksheets and copying matching data to 1 w/sheet

LinkBack

Thread Tools

Rate This Thread

Display

Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Re: Code for searching multiple worksheets and copying matching data to 1 w/sheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions