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 Dear

I don't know how appreciate you.

this is a sample of my actual work.
https://www.dropbox.com/s/g06jn0twmafj71n/sample.xlsx?dl=0

 

[vcoolio]

Hello Alireza,

Is the sample above a different work book? If so, what is it that you wish to do with this work book?

Cheerio,
vcoolio.

 

[alireza123456]

Hi Cheerio
Thanks for your attention.
I have attached another file and i want run last macro that you provide for me(#3) in it.

https://www.dropbox.com/s/2xc5fwm2roldubz/myfile.xlsm?dl=0

sincerely

 

[vcoolio]

Hello Alizera,

The file that you supplied above has thirteen sheets, each with two columns of data. What is it that you would like to do?
Do you want to summarise the data in a summary sheet using the address ID as a criteria?

Could you please give a full and proper description of the inputs and expected results. The code that I supplied in post #3 probably will not do the same task for you in this new file.

Please advise.

Cheerio,
vcoolio.

 

[alireza123456]

Hello Cheerio
this data is household budget survey data.
each sheet is one group of expenditure like:food, housing, ...
i want separate for example 1000 specific address(ID) from all sheet. each address is one household.
i have in other workbook other attributes of household that i should arrange them next.

 

[vcoolio]

Hello Alizera,

So, if you select, for example, the address ID 1000, you would like the data from both columns (from all sheets) associated with that address ID to be summarised in a new sheet. Is this correct?

Cheerio,
vcoolio.

 

[alireza123456]

Hi dear

yes as sample i sent.

 

[alireza123456]

also i want a summary of all sheet as total income of each id

each sheet may have repeated id

 

[vcoolio]

Hello Alireza,

I have adapted the previous code to suit your latest work book as follows:-

Sub SearchShts()

Application.ScreenUpdating = False

        Dim lr As Long
        Dim fSearch As String
        Dim ws As Worksheet
        Dim cell As Range
        Dim ws1 As Worksheet
Set ws1 = Worksheets("Summary")

fSearch = ws1.Range("D1").Value

For Each ws In Worksheets
        If ws.Name <> "Summary" And ws.Name <> "List" Then
        
For Each cell In ws.Range("A2:A" & ws.Cells(Rows.Count, "A").End(xlUp).Row)
                 If cell.Value = fSearch Then
                 lr = ws1.Range("A" & Rows.Count).End(xlUp).Row + 1
                 cell.EntireRow.Copy
                 ws1.Range("A" & lr).PasteSpecial xlPasteValues
                 ws1.Range("C" & lr).Value = ws.Name
                 ws1.Columns.AutoFit
                 End If
             Next
       End If
Next ws

ws1.Range("D1") = "SEARCH"
ws1.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Following is the link to your work book, slightly modified and with the above code implemented:-

https://www.dropbox.com/s/z5nhjan0xvxw6sm/Alireza(2).xlsm?dl=0

You will see that I have added a "Summary" sheet and a "List" sheet. The list sheet simply contains a list of the address IDs in Column A (it has a defined name of "List"). I have just placed a few of the address IDs in this list but all you have to do is add more address IDs to this list and they will appear in the drop down list in cell D1 in the "Summary" sheet. So, select an address ID from the drop down in the "Summary" sheet, click on "GO" and the selected ID details from each sheet will appear in the "Summary" sheet for that particular ID.

From all this, you should now be able to modify/refine your actual work book to suit yourself.

Good luck!

Cheerio,
vcoolio.

 

[alireza123456]

Hi Cheerio
Very very thanks dear,
it exactly works as i want.
please guide me if i want a summary of all sheet as show total income of each ID : id1= ... id2=... , ...
God bless you!
Your sincerely Ali