Copy and Transform

new_too

New Member
Joined
May 2, 2011
Messages
2
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:p></o:p>
<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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; width: 199pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="265"> 21111000183804ORA<o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Provider<o:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> OREGON HEALTH & SCIENCE UNIVERS<o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> 04/13/2011<o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> 04/13/2011<o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" x:num="305.78" nowrap="nowrap" valign="bottom">
$305.78 <o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" x:num="305.78" nowrap="nowrap" valign="bottom">
$305.78 <o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" x:num="69.84" nowrap="nowrap" valign="bottom">
$69.84 <o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" x:num="16.08" nowrap="nowrap" valign="bottom">
$16.08 <o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Outpatient<o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" x:num="2211105200450" nowrap="nowrap" valign="bottom">
2.21111E+12<o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> Provider<o:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> TIMOTHY A JOSLIN JR MD<o:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0.75pt 0.75pt 0in; height: 12.75pt;" nowrap="nowrap" valign="bottom"> <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
</td> </tr> </tbody></table> <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
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:p></o:p>
</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:p></o:p>
</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:p></o:p>
</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:p></o:p>
</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:p></o:p>
</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:p></o:p>
</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:p></o:p>
</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:p></o:p>
</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:p></o:p>
</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:p></o:p>
</td> <td style="padding: 0in; width: 143.25pt; height: 13.5pt;" nowrap="nowrap" valign="bottom" width="191">
AMERICAN MEDICAL RESPONSE NW<o:p></o:p>
</td> <td style="padding: 0in; width: 47.25pt; height: 13.5pt;" nowrap="nowrap" valign="bottom" width="63">
01/06/2011<o:p></o:p>
</td> <td style="padding: 0in; width: 45.75pt; height: 13.5pt;" nowrap="nowrap" valign="bottom" width="61">
01/12/2011<o:p></o:p>
</td> <td style="padding: 0in; width: 46.5pt; height: 13.5pt;" x:num="" nowrap="nowrap" valign="bottom" width="62">
21.25<o:p></o:p>
</td> <td style="padding: 0in; width: 45.75pt; height: 13.5pt;" x:num="" nowrap="nowrap" valign="bottom" width="61">
8.74<o:p></o:p>
</td> <td style="padding: 0in; width: 45.75pt; height: 13.5pt;" x:num="" nowrap="nowrap" valign="bottom" width="61">
6.99<o:p></o:p>
</td> <td style="padding: 0in; width: 36.75pt; height: 13.5pt;" x:num="" nowrap="nowrap" valign="bottom" width="49">
1.75<o:p></o:p>
</td> <td style="padding: 0in; width: 37.5pt; height: 13.5pt;" nowrap="nowrap" valign="bottom" width="50">
PartB<o:p></o:p>
</td> </tr> <tr style="height: 12.75pt;"> <td style="padding: 0in; width: 85.5pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="114">
21102600112104ORA<o:p></o:p>
</td> <td style="padding: 0in; width: 143.25pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="191">
AMI R PATEL<o:p></o:p>
</td> <td style="padding: 0in; width: 47.25pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="63">
01/12/2011<o:p></o:p>
</td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="61">
01/16/2011<o:p></o:p>
</td> <td style="padding: 0in; width: 46.5pt; height: 12.75pt;" x:num="30884.83" nowrap="nowrap" valign="bottom" width="62">
30,884.83<o:p></o:p>
</td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" x:num="30884.83" nowrap="nowrap" valign="bottom" width="61">
30,884.83<o:p></o:p>
</td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" x:num="11179.03" nowrap="nowrap" valign="bottom" width="61">
11,179.03<o:p></o:p>
</td> <td style="padding: 0in; width: 36.75pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="49">
0.00<o:p></o:p>
</td> <td style="padding: 0in; width: 37.5pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="50">
Inpatient<o:p></o:p>
</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">
2,211,045,059,610.00<o:p></o:p>
</td> <td style="padding: 0in; width: 143.25pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="191">
BRONWYN E HAMILTON MD<o:p></o:p>
</td> <td style="padding: 0in; width: 47.25pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="63">
01/12/2011<o:p></o:p>
</td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="61">
01/12/2011<o:p></o:p>
</td> <td style="padding: 0in; width: 46.5pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="62">
21.25<o:p></o:p>
</td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="61">
8.73<o:p></o:p>
</td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="61">
6.98<o:p></o:p>
</td> <td style="padding: 0in; width: 36.75pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="49">
1.75<o:p></o:p>
</td> <td style="padding: 0in; width: 37.5pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="50">
PartB<o:p></o:p>
</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">
2,211,047,035,810.00<o:p></o:p>
</td> <td style="padding: 0in; width: 143.25pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="191">
DAVID B KOSLIN<o:p></o:p>
</td> <td style="padding: 0in; width: 47.25pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="63">
01/12/2011<o:p></o:p>
</td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="61">
01/12/2011<o:p></o:p>
</td> <td style="padding: 0in; width: 46.5pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="62">
21.25<o:p></o:p>
</td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="61">
8.73<o:p></o:p>
</td> <td style="padding: 0in; width: 45.75pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="61">
6.98<o:p></o:p>
</td> <td style="padding: 0in; width: 36.75pt; height: 12.75pt;" x:num="" nowrap="nowrap" valign="bottom" width="49">
1.75<o:p></o:p>
</td> <td style="padding: 0in; width: 37.5pt; height: 12.75pt;" nowrap="nowrap" valign="bottom" width="50">
PartB<o:p></o:p>
</td> </tr> </tbody></table> <!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
Here is the macro I am trying to get to work. The last error message was "loop without do".
<!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
Sub Transpose_data()<o:p></o:p>
'<o:p></o:p>
' Transpose_data Macro<o:p></o:p>
''Set range<o:p></o:p>
Dim iRow As Long, iCol As Long<o:p></o:p>
ActiveCell = 9<o:p></o:p>
iRow = 1<o:p></o:p>
i2Row = 2<o:p></o:p>
iCol = 2<o:p></o:p>
i2Col = 1<o:p></o:p>
' Select cell B9, *first line of data*.<o:p></o:p>
Range("B9").Select<o:p></o:p>
' Set Do loop to stop when an empty cell is reached.<o:p></o:p>
Do Until IsEmpty(ActiveCell)<o:p></o:p>
With Sheet1<o:p></o:p>
Set a = .Range(.Cells(iRow, iCol), .Cells(iRow + 8, iCol))<o:p></o:p>
a.Select<o:p></o:p>
Selection.Copy<o:p></o:p>
Windows("Medical costs.xls").Activate<o:p></o:p>
With medicare<o:p></o:p>
Set b = .Range(.Cells(i2Row, i2Col), .Cells(i2Row + 1, i2Col + 8))<o:p></o:p>
b.Select<o:p></o:p>
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _<o:p></o:p>
, Transpose:=True<o:p></o:p>
<!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
' Step down 9 rows from present location.<o:p></o:p>
ActiveCell.Offset(9, 0).Select<o:p></o:p>
Loop<o:p></o:p>
End Sub
<!--[if !supportEmptyParas]--> <!--[endif]--><o:p></o:p>
If anybody can help I would be delighted
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
ActiveCell = 9 doesn't do anything so i would take it out. Also, you toggle from one workbook to another but don't toggle back.

D
 
Upvote 0
I changed so that data and transposed sheet are in the same workbook. Still getting "loop without do" message
 
Upvote 0

Forum statistics

Threads
1,224,520
Messages
6,179,267
Members
452,902
Latest member
Knuddeluff

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