I am a newbi when it comes to vb so forgive me if I have made some really dumb errors. What I am trying to do is to take a list converted from a word doc and create a new page with the data in 9 columns. The imported data is in rows and in groups of nine
(see below).
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
<table style="width: 287.25pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="383"> <tbody><tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; width: 88.25pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="118"> Claim Number<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; width: 199pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="265"> 21111000183804ORA<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Provider<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> OREGON HEALTH & SCIENCE UNIVERS<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Service Start Date<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> 04/13/2011<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Service End Date<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> 04/13/2011<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Amount Charged<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" x:num="305.78" nowrap="nowrap" valign="bottom"> </td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Medicare Approved<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" x:num="305.78" nowrap="nowrap" valign="bottom"> </td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Provider Paid<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" x:num="69.84" nowrap="nowrap" valign="bottom"> </td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> You May be Billed<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" x:num="16.08" nowrap="nowrap" valign="bottom"> </td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Claim Type<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Outpatient<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Claim Number<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" x:num="2211105200450" nowrap="nowrap" valign="bottom"> </td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Provider<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> TIMOTHY A JOSLIN JR MD<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> </tbody></table> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
This is the format I want to get
<table style="margin-left: -21pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 17.25pt;"> <td style="border-style: solid none solid solid; border-color: windowtext -moz-use-text-color windowtext windowtext; border-width: 1.5pt medium 1.5pt 1.5pt; padding: 0in; width: 85.5pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="114"> Claim Number<o></o>
</td> <td style="border-style: solid none; border-color: windowtext -moz-use-text-color; border-width: 1.5pt medium; padding: 0in; width: 143.25pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="191"> Provider<o></o>
</td> <td style="border-style: solid none; border-color: windowtext -moz-use-text-color; border-width: 1.5pt medium; padding: 0in; width: 47.25pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="63"> Service Start Date<o></o>
</td> <td style="border-style: solid none; border-color: windowtext -moz-use-text-color; border-width: 1.5pt medium; padding: 0in; width: 45.75pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="61"> Service End Date<o></o>
</td> <td style="border-style: solid none; border-color: windowtext -moz-use-text-color; border-width: 1.5pt medium; padding: 0in; width: 46.5pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="62"> Amount Charged<o></o>
</td> <td style="border-style: solid none; border-color: windowtext -moz-use-text-color; border-width: 1.5pt medium; padding: 0in; width: 45.75pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="61"> Medicare Approved<o></o>
</td> <td style="border-style: solid none; border-color: windowtext -moz-use-text-color; border-width: 1.5pt medium; padding: 0in; width: 45.75pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="61"> Provider Paid<o></o>
</td> <td style="border-style: solid none; border-color: windowtext -moz-use-text-color; border-width: 1.5pt medium; padding: 0in; width: 36.75pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="49"> You May be Billed<o></o>
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1.5pt 1.5pt 1.5pt medium; padding: 0in; width: 37.5pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="50"> Claim Type<o></o>
</td> </tr> <tr style="height: 13.5pt;"> <td style="padding: 0in; width: 85.5pt; height: 13.5pt;" x:num="2211046023540" nowrap="nowrap" valign="bottom" width="114"> </td> <td style="padding: 0in; width: 143.25pt; height: 13.5pt;" nowrap="nowrap" valign="bottom" width="191"> </td> <td style="padding: 0in; width: 47.25pt; height: 13.5pt;" nowrap="nowrap" valign="bottom" width="63"> </td> <td style="padding: 0in; width: 45.75pt; height: 13.5pt;" nowrap="nowrap" valign="bottom" width="61"> </td> <td style="padding: 0in; width: 46.5pt; height: 13.5pt;" x:num="" nowrap="nowrap" valign="bottom" width="62"> </td> <td style="padding: 0in; width: 45.75pt; height: 13.5pt;" x:num="" nowrap="nowrap" valign="bottom" width="61"> </td> <td style="padding: 0in; width: 45.75pt; height: 13.5pt;" x:num="" nowrap="nowrap" valign="bottom" width="61"> </td> <td style="padding: 0in; width: 36.75pt; height: 13.5pt;" x:num="" nowrap="nowrap" valign="bottom" width="49"> </td> <td style="padding: 0in; width: 37.5pt; height: 13.5pt;" nowrap="nowrap" valign="bottom" width="50"> </td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0in; width: 85.5pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="114"> </td> <td style="padding: 0in; width: 143.25pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="191"> </td> <td style="padding: 0in; width: 47.25pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="63"> </td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="61"> </td> <td style="padding: 0in; width: 46.5pt; height: 12.75pt;" x:num="30884.83" nowrap="nowrap" valign="bottom" width="62"> </td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" x:num="30884.83" nowrap="nowrap" valign="bottom" width="61"> </td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" x:num="11179.03" nowrap="nowrap" valign="bottom" width="61"> </td> <td style="padding: 0in; width: 36.75pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="49"> </td> <td style="padding: 0in; width: 37.5pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="50"> </td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0in; width: 85.5pt; height: 12.75pt;" x:num="2211045059610" nowrap="nowrap" valign="bottom" width="114"> </td> <td style="padding: 0in; width: 143.25pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="191"> </td> <td style="padding: 0in; width: 47.25pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="63"> </td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="61"> </td> <td style="padding: 0in; width: 46.5pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="62"> </td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="61"> </td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="61"> </td> <td style="padding: 0in; width: 36.75pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="49"> </td> <td style="padding: 0in; width: 37.5pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="50"> </td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0in; width: 85.5pt; height: 12.75pt;" x:num="2211047035810" nowrap="nowrap" valign="bottom" width="114"> </td> <td style="padding: 0in; width: 143.25pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="191"> </td> <td style="padding: 0in; width: 47.25pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="63"> </td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="61"> </td> <td style="padding: 0in; width: 46.5pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="62"> </td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="61"> </td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="61"> </td> <td style="padding: 0in; width: 36.75pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="49"> </td> <td style="padding: 0in; width: 37.5pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="50"> </td> </tr> </tbody></table> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
Here is the macro I am trying to get to work. The last error message was "loop without do".
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
Sub Transpose_data()<o></o>
'<o></o>
' Transpose_data Macro<o></o>
''Set range<o></o>
Dim iRow As Long, iCol As Long<o></o>
ActiveCell = 9<o></o>
iRow = 1<o></o>
i2Row = 2<o></o>
iCol = 2<o></o>
i2Col = 1<o></o>
' Select cell B9, *first line of data*.<o></o>
Range("B9").Select<o></o>
' Set Do loop to stop when an empty cell is reached.<o></o>
Do Until IsEmpty(ActiveCell)<o></o>
With Sheet1<o></o>
Set a = .Range(.Cells(iRow, iCol), .Cells(iRow + 8, iCol))<o></o>
a.Select<o></o>
Selection.Copy<o></o>
Windows("Medical costs.xls").Activate<o></o>
With medicare<o></o>
Set b = .Range(.Cells(i2Row, i2Col), .Cells(i2Row + 1, i2Col + 8))<o></o>
b.Select<o></o>
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _<o></o>
, Transpose:=True<o></o>
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
' Step down 9 rows from present location.<o></o>
ActiveCell.Offset(9, 0).Select<o></o>
Loop<o></o>
End Sub
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
If anybody can help I would be delighted
(see below).
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
<table style="width: 287.25pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="383"> <tbody><tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; width: 88.25pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="118"> Claim Number<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; width: 199pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="265"> 21111000183804ORA<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Provider<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> OREGON HEALTH & SCIENCE UNIVERS<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Service Start Date<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> 04/13/2011<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Service End Date<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> 04/13/2011<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Amount Charged<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" x:num="305.78" nowrap="nowrap" valign="bottom">
$305.78 <o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Medicare Approved<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" x:num="305.78" nowrap="nowrap" valign="bottom">
$305.78 <o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Provider Paid<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" x:num="69.84" nowrap="nowrap" valign="bottom">
$69.84 <o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> You May be Billed<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" x:num="16.08" nowrap="nowrap" valign="bottom">
$16.08 <o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Claim Type<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Outpatient<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Claim Number<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" x:num="2211105200450" nowrap="nowrap" valign="bottom">
2.21111E+12<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Provider<o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> TIMOTHY A JOSLIN JR MD<o></o>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
</td> </tr> </tbody></table> <!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
This is the format I want to get
<table style="margin-left: -21pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 17.25pt;"> <td style="border-style: solid none solid solid; border-color: windowtext -moz-use-text-color windowtext windowtext; border-width: 1.5pt medium 1.5pt 1.5pt; padding: 0in; width: 85.5pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="114"> Claim Number<o></o>
</td> <td style="border-style: solid none; border-color: windowtext -moz-use-text-color; border-width: 1.5pt medium; padding: 0in; width: 143.25pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="191"> Provider<o></o>
</td> <td style="border-style: solid none; border-color: windowtext -moz-use-text-color; border-width: 1.5pt medium; padding: 0in; width: 47.25pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="63"> Service Start Date<o></o>
</td> <td style="border-style: solid none; border-color: windowtext -moz-use-text-color; border-width: 1.5pt medium; padding: 0in; width: 45.75pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="61"> Service End Date<o></o>
</td> <td style="border-style: solid none; border-color: windowtext -moz-use-text-color; border-width: 1.5pt medium; padding: 0in; width: 46.5pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="62"> Amount Charged<o></o>
</td> <td style="border-style: solid none; border-color: windowtext -moz-use-text-color; border-width: 1.5pt medium; padding: 0in; width: 45.75pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="61"> Medicare Approved<o></o>
</td> <td style="border-style: solid none; border-color: windowtext -moz-use-text-color; border-width: 1.5pt medium; padding: 0in; width: 45.75pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="61"> Provider Paid<o></o>
</td> <td style="border-style: solid none; border-color: windowtext -moz-use-text-color; border-width: 1.5pt medium; padding: 0in; width: 36.75pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="49"> You May be Billed<o></o>
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1.5pt 1.5pt 1.5pt medium; padding: 0in; width: 37.5pt; height: 17.25pt;" nowrap="nowrap" valign="bottom" width="50"> Claim Type<o></o>
</td> </tr> <tr style="height: 13.5pt;"> <td style="padding: 0in; width: 85.5pt; height: 13.5pt;" x:num="2211046023540" nowrap="nowrap" valign="bottom" width="114">
2,211,046,023,540.00<o></o>
AMERICAN MEDICAL RESPONSE NW<o></o>
01/06/2011<o></o>
01/12/2011<o></o>
21.25<o></o>
8.74<o></o>
6.99<o></o>
1.75<o></o>
PartB<o></o>
21102600112104ORA<o></o>
AMI R PATEL<o></o>
01/12/2011<o></o>
01/16/2011<o></o>
30,884.83<o></o>
30,884.83<o></o>
11,179.03<o></o>
0.00<o></o>
Inpatient<o></o>
2,211,045,059,610.00<o></o>
BRONWYN E HAMILTON MD<o></o>
01/12/2011<o></o>
01/12/2011<o></o>
21.25<o></o>
8.73<o></o>
6.98<o></o>
1.75<o></o>
PartB<o></o>
2,211,047,035,810.00<o></o>
DAVID B KOSLIN<o></o>
01/12/2011<o></o>
01/12/2011<o></o>
21.25<o></o>
8.73<o></o>
6.98<o></o>
1.75<o></o>
PartB<o></o>
Here is the macro I am trying to get to work. The last error message was "loop without do".
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
Sub Transpose_data()<o></o>
'<o></o>
' Transpose_data Macro<o></o>
''Set range<o></o>
Dim iRow As Long, iCol As Long<o></o>
ActiveCell = 9<o></o>
iRow = 1<o></o>
i2Row = 2<o></o>
iCol = 2<o></o>
i2Col = 1<o></o>
' Select cell B9, *first line of data*.<o></o>
Range("B9").Select<o></o>
' Set Do loop to stop when an empty cell is reached.<o></o>
Do Until IsEmpty(ActiveCell)<o></o>
With Sheet1<o></o>
Set a = .Range(.Cells(iRow, iCol), .Cells(iRow + 8, iCol))<o></o>
a.Select<o></o>
Selection.Copy<o></o>
Windows("Medical costs.xls").Activate<o></o>
With medicare<o></o>
Set b = .Range(.Cells(i2Row, i2Col), .Cells(i2Row + 1, i2Col + 8))<o></o>
b.Select<o></o>
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _<o></o>
, Transpose:=True<o></o>
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
' Step down 9 rows from present location.<o></o>
ActiveCell.Offset(9, 0).Select<o></o>
Loop<o></o>
End Sub
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
If anybody can help I would be delighted