ZVI,
Okay, here's what's going on..(not sure if this matters, but I'm using Excel and Word 2002 at home and have Excel and Word 2003 at work. This is being tested on 2002 versions.
1. Currently, the Word document must already be open, otherwise I get an error, can it be set to open the Word doc up if it's not already open?
2. I set up and tested the Word template with ALL 20 FIELDS intially and nothing seemed to work. Then, I erased ALL FIELDS except for one, FIELD20. I tried both form locking and form unlocking and this is what I got on the Word end...
I'm not sure that the code is still set to copy the active range of cells, wherever they may be, because I see 2 cell range references of cells.
The first range in your code is this..
Code:
For Each c In Sh.Range("G1:G29")
The second range in your code is this
Code:
For Each c In Sh.Range("E1:E9")
Is the code currently written in such a way that I must have my active cell range be in either of these 2 ranges? I would like to be able to have Excel copy the range that I select, wherever that may be (the range of cells will always be different, everytime I run my report).
So, I'm not sure that this was the way that I was supposed to do this, but I selected my own active range of cells which, this time was
which, in Excel, looks like this...
<TABLE style="WIDTH: 262pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=348 x:str><COLGROUP><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2588" width=73><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2076" width=58><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2389" width=67><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2730" width=77><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2588" width=73><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: purple; WIDTH: 55pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=19 width=73>
Trip Type</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: purple; WIDTH: 44pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=58>
Character</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: purple; WIDTH: 50pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=67></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: purple; WIDTH: 58pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl23 width=77></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: purple; WIDTH: 55pt; BORDER-TOP: windowtext 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl24 width=73></TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: purple; HEIGHT: 13.8pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=18></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: purple; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>
Daisy Duck</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: purple; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>
Donald Duck</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: purple; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26>
Mickey Mouse</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: purple; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl27>
Minny Mouse</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 height=18>
BOREDOM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right x:num>
2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right x:num>
6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right x:num>
2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl30 align=right x:num>
2</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 height=18>
BUSINESS</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right x:num>
3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right x:num>
5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right x:num>
1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl30 align=right x:num>
4</TD></TR><TR style="HEIGHT: 13.8pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.8pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 height=18>
PLEASURE</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right x:num>
2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right x:num>
4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 align=right x:num>
4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl30 align=right x:num>
2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 height=19>
Grand Total</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right x:num>
7</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right x:num>
15</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 align=right x:num>
7</TD><TD style="BORDER-BOTTOM: windowtext 1.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl33 align=right x:num>
8</TD></TR></TBODY></TABLE>
After I ran the macro (with the Word document pre-open), the cell range above pasted into FIELD20 of the Word document (tried it both locked and unlocked) and the result was this...
Donald Duck 6 5 4 15 (blank) 1 1
Any thoughts? and again, many thanks