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

Status
Not open for further replies.

cbk40060

New Member
Joined
Jul 5, 2018
Messages
13
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 CodeProduct NameClosing StockTotal Sales
1123452395A10080
2133452390B20060
3123452430C300100
4113502435D40090
5113502445E500120
6123452455F600345
7113452470G700250
8113692475H800600
Page Total:36001645

<tbody>
</tbody>
Sheet1 (“Table1”)

Shop1 Opening Balance01250
Shop1 Closing Balance00570

<tbody>
</tbody>
Sheet2 (“Table2”)


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

<tbody>
</tbody>
Sheet3 (“Table3”)


Sr. No.Product CodeProduct NameClosing StockTotal Sales
1123452480I900630
2113452485J1000280
3123452430K500370
4113502435L800385
5113502445M700400
6123452455N650395
7113452470O900675
8113692475P850295
Page Total:63003430

<tbody>
</tbody>
Sheet4 (“Table4”)


Shop2 Opening Balance03750
Shop2 Closing Balance01045

<tbody>
</tbody>
Sheet5 (“Table5”)

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

<tbody>
</tbody>
Sheet6 (“Table6”)


Sr. No.Product CodeProduct NameClosing StockTotal Sales
1123452395Q1235645
2123752390R650270
3123452430S880390
4113502435T360270
5113502445U15651045
6123452455V750625
7113452470W980480
8113692475X765385
Page Total:71854110

<tbody>
</tbody>
Sheet7 (“Table7”)


Shop3 Opening Balance08350
Shop3 Closing Balance05230

<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 ProductCash Sales or Credit SalesProduct CodeProduct descriptionTotal Sales
1PCash Sale123452395A80
2QCash Sale123452430B100
3RCash Sale123452455C345
4SCredit Sale123452480D630
5TCash Sale123452430E370
6UCredit Sale123452455F395
7VCredit Sale123452395G645
8WCash Sale123452430Please help me to modify this macro, so as to extract the desired data from all the sheets of the file.

<tbody>
</tbody>

H390
9XCredit Sale123452455I625

<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 ProductCash Sales or Credit SalesProduct CodeProduct descriptionTotal Sales
1PCash Sale123452395A80
2QCash Sale123452430B100
3RCash Sale123452455C345
4SCredit SaleD
5TCash SaleE
6UCredit SaleF
7VCredit SaleG
8WCash SaleH
9XCredit SaleI

<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 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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
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.

 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,130,206
Messages
5,640,839
Members
417,173
Latest member
Tuanphun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top