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 spec

[cbk40060]

Hi.
I am CHANDRASHEKHAR.
I am relatively new to writing excel macros.
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:

Sr. No.	Product Code	Product Name	Closing Stock	Total Sales
1	123452395	A	100	80
2	133452390	B	200	60
3	123452430	C	300	100
4	113502435	D	400	90
5	113502445	E	500	120
6	123452455	F	600	345
7	113452470	G	700	250
8	113692475	H	800	600
Page Total:	3600	1645

<tbody>
</tbody>

Sheet1 (“Table1”)

Shop1 Opening Balance	0	1	2	5	0
Shop1 Closing Balance	0	0	5	7	0

<tbody>
</tbody>

Sheet2 (“Table2”)

Order Number	:-	300718025113
Order Date	:-	29-Jul-18

<tbody>
</tbody>

Sheet3 (“Table3”)

Sr. No.	Product Code	Product Name	Closing Stock	Total Sales
1	123452480	I	900	630
2	113452485	J	1000	280
3	123452430	K	500	370
4	113502435	L	800	385
5	113502445	M	700	400
6	123452455	N	650	395
7	113452470	O	900	675
8	113692475	P	850	295
Page Total:	6300	3430

<tbody>
</tbody>

Sheet4 (“Table4”)

Shop2 Opening Balance	0	3	7	5	0
Shop2 Closing Balance	0	1	0	4	5

<tbody>
</tbody>

Sheet5 (“Table5”)

Order Number	:-	300718032450
Order Date	:-	31-Jul-18

<tbody>
</tbody>

Sheet6 (“Table6”)

Sr. No.	Product Code	Product Name	Closing Stock	Total Sales
1	123452395	Q	1235	645
2	123752390	R	650	270
3	123452430	S	880	390
4	113502435	T	360	270
5	113502445	U	1565	1045
6	123452455	V	750	625
7	113452470	W	980	480
8	113692475	X	765	385
Page Total:	7185	4110

<tbody>
</tbody>

Sheet7 (“Table7”)

Shop3 Opening Balance	0	8	3	5	0
Shop3 Closing Balance	0	5	2	3	0

<tbody>
</tbody>

Sheet8 (“Table8”)

Order Number	:-	300718041355
Order Date	:-	31-Jul-18

<tbody>
</tbody>

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 result of the paste on the Column D & Column F of sheets of the different file should look like this :
Column D Column F

Product Code	Total Sales
123452395	80
123452430	100
123452455	345
123452480	630
123452430	370
123452455	395
123452395	645
123452430	390
123452455	625

<tbody>
</tbody>

I am giving below the drop box links of the three files shared by me in this connection :

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

https://www.dropbox.com/s/n823nxgkhv401jg/source file.xlsx?dl=0

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

Please help me regarding the excel macro.

 

[cbk40060]

I have modified my previous post, posted on 03-08-2018, as under:

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:

Sr. No.	Product Code	Product Name	Closing Stock	Total Sales
1	123452395	A	100	80
2	133452390	B	200	60
3	123452430	C	300	100
4	113502435	D	400	90
5	113502445	E	500	120
6	123452455	F	600	345
7	113452470	G	700	250
8	113692475	H	800	600
Page Total:	3600	1645

<tbody>
</tbody>

Sheet1 (“Table1”)

Shop1 Opening Balance	0	1	2	5	0
Shop1 Closing Balance	0	0	5	7	0

<tbody>
</tbody>

Sheet2 (“Table2”)

Order Number	:-	300718025113
Order Date	:-	29-Jul-18

<tbody>
</tbody>

Sheet3 (“Table3”)

Sr. No.	Product Code	Product Name	Closing Stock	Total Sales
1	123452480	I	900	630
2	113452485	J	1000	280
3	123452430	K	500	370
4	113502435	L	800	385
5	113502445	M	700	400
6	123452455	N	650	395
7	113452470	O	900	675
8	113692475	P	850	295
Page Total:	6300	3430

<tbody>
</tbody>

Sheet4 (“Table4”)

Shop2 Opening Balance	0	3	7	5	0
Shop2 Closing Balance	0	1	0	4	5

<tbody>
</tbody>

Sheet5 (“Table5”)

Order Number	:-	300718032450
Order Date	:-	31-Jul-18

<tbody>
</tbody>

Sheet6 (“Table6”)

Sr. No.	Product Code	Product Name	Closing Stock	Total Sales
1	123452395	Q	1235	645
2	123752390	R	650	270
3	123452430	S	880	390
4	113502435	T	360	270
5	113502445	U	1565	1045
6	123452455	V	750	625
7	113452470	W	980	480
8	113692475	X	765	385
Page Total:	7185	4110

<tbody>
</tbody>

Sheet7 (“Table7”)

Shop3 Opening Balance	0	8	3	5	0
Shop3 Closing Balance	0	5	2	3	0

<tbody>
</tbody>

Sheet8 (“Table8”)

Order Number	:-	300718041355
Order Date	:-	31-Jul-18

<tbody>
</tbody>

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:

Sl. No.	Name of the Product	Cash Sales or Credit Sales	Product Code	Product description	Total Sales
1	P	Cash Sale	123452395	A	80
2	Q	Cash Sale	123452430	B	100
3	R	Cash Sale	123452455	C	345
4	S	Credit Sale	123452480	D	630
5	T	Cash Sale	123452430	E	370
6	U	Credit Sale	123452455	F	395
7	V	Credit Sale	123452395	G	645
8	W	Cash Sale	123452430	H	390
9	X	Credit Sale	123452455	I	625

<tbody>
</tbody>

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:

Sl. No.	Name of the Product	Cash Sales or Credit Sales	Product Code	Product description	Total Sales
1	P	Cash Sale	123452395	A	80
2	Q	Cash Sale	123452430	B	100
3	R	Cash Sale	123452455	C	345
4	S	Credit Sale		D
5	T	Cash Sale		E
6	U	Credit Sale		F
7	V	Credit Sale		G
8	W	Cash Sale		H
9	X	Credit Sale		I

<tbody>
</tbody>

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 new files in dropbox websites and the links to those four new files are given below:

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


My present macro is in the 'source file.xlsm' uploaded by me to drop box, today, on 05-08-2018.

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

- CHANDRASHEKHAR.