copy condtional column

E

emeris

New Member
Joined
Mar 20, 2009
Messages
8
hi I have problem in consolidating all the data into one workbooks.
basically I wan to consolidate all the data into the summary workbook, where each data are copied into the correct heading. The format to be used are found in the each worksheet of the summary workbook.

I have the following.
- A summary workbook with "menu" worksheet and other worksheets.

In the "menu" worksheet,
Menu
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 173px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="background-color: rgb(255, 204, 0);">Directory</td><td>C:\today\complie\</td></tr></tbody></table>Example : In "sheet1" worksheet of the summary workbook
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 70px;"><col style="width: 64px;"><col style="width: 85px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="background-color: rgb(204, 255, 204);">File</td><td>Trade ID</td><td>Trade Number</td><td>Folder</td><td>B/S</td><td>Cpty Name</td><td>Trade Date</td><td>Pair</td><td>Spot Rate</td><td>Amount1</td></tr></tbody></table>
I would like to have a macro to :

- Go to folder, specified by B2.
- In every file opened up, if the worksheet name of the active workbook is the same with one of the worksheet in the summary workbook. it will do the following:

In the row 2 of the active worksheet, are the headings whereas the headings in the worksheet of the summary workbook are found in row 1.

- for each same heading between the 2 worksheets, it will copy the data under that column found in the active worksheets and paste to the summary workbook.

- whereas column A will show the file name of the worksheet copied.

- if the heading in the activeworksheet is not found in the summaryworkbook, nothing in that column will be copied.

- trade ID in column B of the summary workbook is the unique ID. thus for every new copy of data over will be copied in the same row as the respective trade ID

- The process will end when the it run through all the worksheets of all the files in the folder.

note : the worksheet name in the summary workbook are the same as the files in the folder.

Example:
010309.xls in folder
sheet1
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>Trade ID</td><td>Trade Number</td><td>Folder</td><td>B/S</td><td>Cpty Name</td><td>Trade Date</td><td>Pair</td><td>Spot Rate</td><td>Amount1</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>A1</td><td>A2</td><td>A3</td><td>A4</td><td>A6</td><td>A7</td><td>A8</td><td>A9</td><td>A10</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td>B1</td><td>
</td><td>B3</td><td>B4</td><td>B6</td><td>B7</td><td>B8</td><td>B9</td><td>B10</td></tr></tbody></table>
020309.xls in folder
Sheet1
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td>Trade ID</td><td>Trade Number</td><td>Folder</td><td>B/S</td><td>Cpty Name</td><td>Trade Date</td><td>Missing 1</td><td>Pair</td><td>Spot Rate</td><td>Amount1</td><td>Missing 2</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td>C1</td><td>C2</td><td>C3</td><td>C4</td><td>C6</td><td>C7</td><td>
</td><td>C8</td><td>C9</td><td>C10</td><td>
</td></tr></tbody></table>
final output in summary workbook
sheet1
<table style="font-family: Arial,Arial; font-size: 10pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 70px;"><col style="width: 64px;"><col style="width: 85px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td><td style="background-color: rgb(204, 255, 204);">File</td><td>Trade ID</td><td>Trade Number</td><td>Folder</td><td>B/S</td><td>Cpty Name</td><td>Trade Date</td><td>Pair</td><td>Spot Rate</td><td>Amount1</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td><td style="background-color: rgb(204, 255, 204);">010309.xls</td><td>A1</td><td>A2</td><td>A3</td><td>A4</td><td>A6</td><td>A7</td><td>A8</td><td>A9</td><td>A10</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="background-color: rgb(204, 255, 204);">010309.xls</td><td>B1</td><td>
</td><td>B3</td><td>B4</td><td>B6</td><td>B7</td><td>B8</td><td>B9</td><td>B10</td></tr><tr style="height: 17px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="background-color: rgb(204, 255, 204);">020309.xls</td><td>C1</td><td>C2</td><td>C3</td><td>C4</td><td>C6</td><td>C7</td><td>C8</td><td>C9</td><td>C10</td></tr></tbody></table>
 
Sort by date Sort by votes
Hi emeris,

A tall order but I'll have a go at putting something together for you.

As a matter of interest, other than "Menu", how many Summary Worksheets do you have in the Summary WB.

Regards

ColinKJ
 
Upvote 0
Hi emeris,

I've put something together, if you send me a private message with an email address, I'll send it over.

ColinKJ
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
import specific columns from one workbook to another from matching header with vba
Replies
1
Views
258
Peter_SSs
Peter_SSs
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
963
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
711
mumps
mumps
C
  • Question Question
Picture won't paste in cell overlay
Replies
0
Views
268
Cerka
C
E
  • Question Question
Adding a custom message.
Replies
1
Views
715
Micron
M

Share this page

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
Back
Top