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:
[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 result of the paste on the Column D & Column F of sheets of the different file should look like this :
Column D Column F
[TABLE="width: 179"]
<tbody>[TR]
[TD]Product Code[/TD]
[TD]Total Sales[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]123452395[/TD]
[TD]80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452430[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452455[/TD]
[TD]345[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452480[/TD]
[TD]630[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452430[/TD]
[TD]370[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452455[/TD]
[TD]395[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452395[/TD]
[TD]645[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452430[/TD]
[TD]390[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452455[/TD]
[TD]625[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.
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:
[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 result of the paste on the Column D & Column F of sheets of the different file should look like this :
Column D Column F
[TABLE="width: 179"]
<tbody>[TR]
[TD]Product Code[/TD]
[TD]Total Sales[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]123452395[/TD]
[TD]80[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452430[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452455[/TD]
[TD]345[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452480[/TD]
[TD]630[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452430[/TD]
[TD]370[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452455[/TD]
[TD]395[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452395[/TD]
[TD]645[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452430[/TD]
[TD]390[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]123452455[/TD]
[TD]625[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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.