Macro to conditionally copy from excel to word

[jj620]

Hi

I'm getting to grips with writing vba but I wondered if anyone could help me with a query.

I have an Excel Workbook with 10 sheets within it. Each sheet is for a different company and each sheet has a list of suppliers within it.

Basically I have suppliers listed as 1 2 and 3 (baed in the coloured cell H) depending on their importance and need a macro to go across all the worksheets and create a report in word, this report should look at all the data in all the worksheets and the first word document should show all the suppliers listed as 1, the second show all suppliers listed as 2, and so on.

I need the entire row of data to export in one bulk from all the sheets into 1 report

<TABLE style="WIDTH: 720pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=960><COLGROUP><COL style="WIDTH: 48pt" span=15 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 height=17 width=64>A</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 width=64>B</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 width=64>C</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 width=64>D</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 width=64>E</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 width=64>F</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 width=64>G</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 width=64>H</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 width=64>I</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 width=64>J</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 width=64>K</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 width=64>L</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 width=64>M</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 width=64>N</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl81 width=64>O</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl75 height=20>James</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl77>Substantial</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl70>VL</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl70>V</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl70>MM</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl78>K</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl71></TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: red; COLOR: red; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: silver 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl72>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl72></TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl72></TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>14</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl71>75</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl72>17</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>C</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: silver; BORDER-RIGHT: #f0f0f0" class=xl76 height=20>Stu</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: #f0f0f0" class=xl79>Substantial</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65>VL</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65>J</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl65>HM</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: windowtext 0.5pt solid" class=xl80>T</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl67></TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: red; COLOR: red; FONT-SIZE: 11pt; BORDER-TOP: silver 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: silver 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl66>1</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66></TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66>Y</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>11</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl67>85</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66>21</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>C</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: silver; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69></TD></TR></TBODY></TABLE>

Any help to see if this is possible would be great? Does that all make sense?

J

 

[jj620]

Can anyone help with this macro?