I need modifications in my macro to copy the cell values containing a particular substring and the values against those cells in the third right colum

[cbk40060]

I have an excel file having nine sheets.
Each sheet is having different tables.
I need a macro to copy the cell values containing a particular substring and the values against those cells in the third right column and paste the same in two specific columns of excel sheet of a different excel file.
Source data is as under:
[TABLE="width: 321"]
<tbody>[TR]
[TD]Sr. No.[/TD]
[TD]Product Code[/TD]
[TD]Product Name[/TD]
[TD]Closing Stock[/TD]
[TD]Total Sales[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123452395[/TD]
[TD]A[/TD]
[TD]100[/TD]
[TD]80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]133452390[/TD]
[TD]B[/TD]
[TD]200[/TD]
[TD]60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]123452430[/TD]
[TD]C[/TD]
[TD]300[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]113502435[/TD]
[TD]D[/TD]
[TD]400[/TD]
[TD]90[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]113502445[/TD]
[TD]E[/TD]
[TD]500[/TD]
[TD]120[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]123452455[/TD]
[TD]F[/TD]
[TD]600[/TD]
[TD]345[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]113452470[/TD]
[TD]G[/TD]
[TD]700[/TD]
[TD]250[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]113692475[/TD]
[TD]H[/TD]
[TD]800[/TD]
[TD]600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Page Total:[/TD]
[TD]3600[/TD]
[TD]1645[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet1 (“Table1”)

[TABLE="width: 475"]
<tbody>[TR]
[TD]Shop1 Opening Balance[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Shop1 Closing Balance[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]7[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
Sheet2 (“Table2”)


[TABLE="width: 498"]
<tbody>[TR]
[TD]Order Number[/TD]
[TD]:-[/TD]
[TD]300718025113[/TD]
[/TR]
[TR]
[TD]Order Date[/TD]
[TD]:-[/TD]
[TD]29-Jul-18[/TD]
[/TR]
</tbody>[/TABLE]
Sheet3 (“Table3”)


[TABLE="width: 328"]
<tbody>[TR]
[TD]Sr. No.[/TD]
[TD]Product Code[/TD]
[TD]Product Name[/TD]
[TD]Closing Stock[/TD]
[TD]Total Sales[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123452480[/TD]
[TD]I[/TD]
[TD]900[/TD]
[TD]630[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]113452485[/TD]
[TD]J[/TD]
[TD]1000[/TD]
[TD]280[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]123452430[/TD]
[TD]K[/TD]
[TD]500[/TD]
[TD]370[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]113502435[/TD]
[TD]L[/TD]
[TD]800[/TD]
[TD]385[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]113502445[/TD]
[TD]M[/TD]
[TD]700[/TD]
[TD]400[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]123452455[/TD]
[TD]N[/TD]
[TD]650[/TD]
[TD]395[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]113452470[/TD]
[TD]O[/TD]
[TD]900[/TD]
[TD]675[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]113692475[/TD]
[TD]P[/TD]
[TD]850[/TD]
[TD]295[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Page Total:[/TD]
[TD]6300[/TD]
[TD]3430[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet4 (“Table4”)


[TABLE="width: 475"]
<tbody>[TR]
[TD]Shop2 Opening Balance[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Shop2 Closing Balance[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]
Sheet5 (“Table5”)

[TABLE="width: 498"]
<tbody>[TR]
[TD]Order Number[/TD]
[TD]:-[/TD]
[TD]300718032450[/TD]
[/TR]
[TR]
[TD]Order Date[/TD]
[TD]:-[/TD]
[TD]31-Jul-18[/TD]
[/TR]
</tbody>[/TABLE]
Sheet6 (“Table6”)


[TABLE="width: 328"]
<tbody>[TR]
[TD]Sr. No.[/TD]
[TD]Product Code[/TD]
[TD]Product Name[/TD]
[TD]Closing Stock[/TD]
[TD]Total Sales[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123452395[/TD]
[TD]Q[/TD]
[TD]1235[/TD]
[TD]645[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123752390[/TD]
[TD]R[/TD]
[TD]650[/TD]
[TD]270[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]123452430[/TD]
[TD]S[/TD]
[TD]880[/TD]
[TD]390[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]113502435[/TD]
[TD]T[/TD]
[TD]360[/TD]
[TD]270[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]113502445[/TD]
[TD]U[/TD]
[TD]1565[/TD]
[TD]1045[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]123452455[/TD]
[TD]V[/TD]
[TD]750[/TD]
[TD]625[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]113452470[/TD]
[TD]W[/TD]
[TD]980[/TD]
[TD]480[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]113692475[/TD]
[TD]X[/TD]
[TD]765[/TD]
[TD]385[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Page Total:[/TD]
[TD]7185[/TD]
[TD]4110[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Sheet7 (“Table7”)


[TABLE="width: 475"]
<tbody>[TR]
[TD]Shop3 Opening Balance[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Shop3 Closing Balance[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
Sheet8 (“Table8”)


[TABLE="width: 498"]
<tbody>[TR]
[TD]Order Number[/TD]
[TD]:-[/TD]
[TD]300718041355[/TD]
[/TR]
[TR]
[TD]Order Date[/TD]
[TD]:-[/TD]
[TD]31-Jul-18[/TD]
[/TR]
</tbody>[/TABLE]
Sheet9 (“Table9”)

Requirement:
I need the macro search all the nine sheets of the excel file for a substring of “12345” and copy the cell-values containing such substring, and also copy the values of the cells against the third right column of such cells. And then paste those values in two specific columns of the sheet of another different excel file.
The desired result of the paste on the Column D & Column F of sheets of the different file, should be as under:

[TABLE="width: 463"]
<tbody>[TR]
[TD]Sl. No.[/TD]
[TD]Name of the Product[/TD]
[TD]Cash Sales or Credit Sales[/TD]
[TD]Product Code[/TD]
[TD]Product description[/TD]
[TD]Total Sales[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]P[/TD]
[TD]Cash Sale[/TD]
[TD]123452395[/TD]
[TD]A[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Q[/TD]
[TD]Cash Sale[/TD]
[TD]123452430[/TD]
[TD]B[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]R[/TD]
[TD]Cash Sale[/TD]
[TD]123452455[/TD]
[TD]C[/TD]
[TD]345[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]S[/TD]
[TD]Credit Sale[/TD]
[TD]123452480[/TD]
[TD]D[/TD]
[TD]630[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]T[/TD]
[TD]Cash Sale[/TD]
[TD]123452430[/TD]
[TD]E[/TD]
[TD]370[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]U[/TD]
[TD]Credit Sale[/TD]
[TD]123452455[/TD]
[TD]F[/TD]
[TD]395[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]V[/TD]
[TD]Credit Sale[/TD]
[TD]123452395[/TD]
[TD]G[/TD]
[TD]645[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]W[/TD]
[TD]Cash Sale[/TD]
[TD]123452430Please help me to modify this macro, so as to extract the desired data from all the sheets of the file.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 463"]
<tbody>[TR]
[TD]H[/TD]
[TD]390[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]X[/TD]
[TD]Credit Sale[/TD]
[TD]123452455[/TD]
[TD]I[/TD]
[TD]625[/TD]
[/TR]
</tbody>[/TABLE]
There are nine sheets in the source file.
Only the sheets 1,4 & 7 contain the desired data.
And the other sheets, i.e., sheets 2,3,5,6,8 & 9, do not contain the desired data.
I have found a macro online and with the help of that macro, I am able to extract the desired data from only the first sheet of the source file.
The present result is as under:
[TABLE="width: 480"]
<tbody>[TR]
[TD]Sl. No.[/TD]
[TD]Name of the Product[/TD]
[TD]Cash Sales or Credit Sales[/TD]
[TD]Product Code[/TD]
[TD]Product description[/TD]
[TD]Total Sales[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]P[/TD]
[TD]Cash Sale[/TD]
[TD]123452395[/TD]
[TD]A[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Q[/TD]
[TD]Cash Sale[/TD]
[TD]123452430[/TD]
[TD]B[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]R[/TD]
[TD]Cash Sale[/TD]
[TD]123452455[/TD]
[TD]C[/TD]
[TD]345[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]S[/TD]
[TD]Credit Sale[/TD]
[TD][/TD]
[TD]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]T[/TD]
[TD]Cash Sale[/TD]
[TD][/TD]
[TD]E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]U[/TD]
[TD]Credit Sale[/TD]
[TD][/TD]
[TD]F[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]V[/TD]
[TD]Credit Sale[/TD]
[TD][/TD]
[TD]G[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]W[/TD]
[TD]Cash Sale[/TD]
[TD][/TD]
[TD]H[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]X[/TD]
[TD]Credit Sale[/TD]
[TD][/TD]
[TD]I[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I do not know how to extract the desired data from the remaining sheets , i.e., sheet4 & sheet7.

In this connection, I have uploaded four files in dropbox website and I am sharing the links of those four files, hereunder:
https://www.dropbox.com/s/4cjwk8p1jlcf13l/source file.xlsm?dl=0

https://www.dropbox.com/s/2b4962tomtwo6g3/Present Result file.xlsx?dl=0

https://www.dropbox.com/s/of2n84cg1wq9xbg/Desired Result file.xlsx?dl=0

https://www.dropbox.com/s/cam7e9uhh2drsqc/WORK1 QUERY.docx?dl=0


Please help me to modify this macro, so as to extract the desired data from all the sheets of the file.

- CHANDRASHEKHAR.

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...l-sheet-different-excel-file.html#post5118127

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.