separation of data

[alireza123456]

Hi
I have a large number of factory data in different sheets and wish separate a specified factory data. please guide me.
for example:
sheet1:

factory	sale	income	work day
a	10	200	5
b	20	300	6
c	15	300	5
d	5	500	4

<colgroup><col width="70" span="4" style="width:53pt"> </colgroup><tbody>
</tbody>

sheet2:

factory	sale	income	work day
b	10	100	3
d	12	500	4
a	14	200	5
f	13	300	6
h	10	400	5

<colgroup><col width="70" span="4" style="width:53pt"> </colgroup><tbody>
</tbody>

sheet 3:

factory	sale	income	work day
a	10	200	5
c	20	300	4
f	30	400	6
a	5	200	5

<colgroup><col width="70" span="4" style="width:53pt"> </colgroup><tbody>
</tbody>

i want:

factory	sale	income	work day
a	10	200	5
a	14	200	5
a	10	200	5
a	5	200	5

<colgroup><col width="70" span="4" style="width:53pt"> </colgroup><tbody>
</tbody>

 

[alireza123456]

Hi
I have a large number of factory data in different sheets and wish separate a specified factory data. please guide me.
for example:
sheet1:

factory	sale	income	work day
a	10	200	5
b	20	300	6
c	15	300	5
d	5	500	4

<tbody>
</tbody>

sheet2:

factory	sale	income	work day
b	10	100	3
d	12	500	4
a	14	200	5
f	13	300	6
h	10	400	5

<tbody>
</tbody>

sheet 3:

factory	sale	income	work day
a	10	200	5
c	20	300	4
f	30	400	6
a	5	200	5

<tbody>
</tbody>

i want:

factory	sale	income	work day
a	10	200	5
a	14	200	5
a	10	200	5
a	5	200	5

<tbody>
</tbody>

This quesion also come in :

separation of data

 

[vcoolio]

Hello Alireza,

Perhaps the following code will do the task for you:-

Sub SearchShts()

Application.ScreenUpdating = False

        Dim lr As Long
        Dim fSearch As String
        Dim ws As Worksheet
        Dim cell As Range

fSearch = Sheet1.Range("F1").Value

For Each ws In Worksheets
        If ws.Name <> "Master" Then
        
For Each cell In ws.Range("A2:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
                 If cell.Value = fSearch Then
                 lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row + 1
                 cell.EntireRow.Copy
                 Sheet1.Range("A" & lr).PasteSpecial xlPasteValues
                 Sheet1.Range("E" & lr).Value = ws.Name
                 End If
             Next
       End If
Next ws

Sheet1.Range("F1") = "SEARCH"
Sheet1.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code checks a search box in cell F1 (in the Master sheet) for a value selected from a drop down list and then searches all sheets for that value. It then transfers all the relevant data related to that value to the Master sheet.
The source sheet is also placed in Column E of the Master sheet.

Following is the link to my test work book. Select a value from the drop down in the search box then click on "GO" to see it work.

https://www.dropbox.com/s/3x4w8sniphym8mz/Alireza(multi shts to master sht).xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.

 

[alireza123456]

very very thanks Cheerio
if i add a row to sheet 1 :
W 10 100 5
then w don't add to search cell in master sheet.
what is your suggestion?

 

[vcoolio]

Hello Alireza,

In the code above, sheet1 is the "Master" sheet so you shouldn't be adding anything to it. But I do assume that you mean the source sheets.
So, back in the Master sheet, you need to add W to the data validation drop down. It will then work.

Cheerio,
vcoolio.

 

[alireza123456]

i have changed name in sheet 2 :4
but search in master has not change

a to ali
b to baba
...

 

[alireza123456]

very very thanks Cheerio
if i add a row to sheet 2 :
W 10 100 5
then w don't add to search cell in master sheet.
what is your suggestion?

 

[vcoolio]

As I said in post #5 above, you need to add the W to the data validation drop down list which is in cell F1 in the sample I supplied.

Cheerio,
vcoolio.

 

[alireza123456]

thanks, i understand now.
pardon me. i am novic.
Did you mind if i have simple questio.
who i can use this macro in other worksheet?

please notice i'm novic. if you have time, please tell me step by step.

 

[vcoolio]

Hello Alireza,

I will do that for you but later today/tonight. In the meantime, could please upload a sample of your actual work book to a free file sharing site (such as DropBox) and then post the link to your file back here. Please use dummy data.

Cheerio,
vcoolio.