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