Copy sheet and add Location number

[kenpcli]

I am trying get a macro to look down column A and each change in site name add their location number underneath it.

[TABLE="width: 889"]
<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Chg Amt[/TD]
[TD]Pay Amt[/TD]
[TD]Adj Amt[/TD]
[TD]Ref Amt[/TD]
[TD]Bal Amt[/TD]
[/TR]
[TR]
[TD]Albuquerque[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For AETNA[/TD]
[TD]$51,896.00[/TD]
[TD]-$31,204.59[/TD]
[TD]-$19,152.69[/TD]
[TD]$164.35[/TD]
[TD]$1,703.07[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For BCBS[/TD]
[TD]$659,678.00[/TD]
[TD]-$320,482.96[/TD]
[TD]-$324,294.18[/TD]
[TD]$7,687.05[/TD]
[TD]$22,587.91[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For CIGNA[/TD]
[TD]$264.00[/TD]
[TD]-$211.20[/TD]
[TD]-$52.80[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For COMMERCIAL[/TD]
[TD]$20,122.00[/TD]
[TD]-$13,354.59[/TD]
[TD]-$4,563.70[/TD]
[TD]$0.00[/TD]
[TD]$2,203.71[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For DSHS[/TD]
[TD]$8,573.00[/TD]
[TD]-$4,020.36[/TD]
[TD]-$60.64[/TD]
[TD]$0.00[/TD]
[TD]$4,492.00[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For FCHN[/TD]
[TD]$3,774.00[/TD]
[TD]-$2,868.21[/TD]
[TD]-$905.79[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For HUMANA[/TD]
[TD]$309.00[/TD]
[TD]-$167.12[/TD]
[TD]-$141.88[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For IPN[/TD]
[TD]$3,923.00[/TD]
[TD]-$2,678.76[/TD]
[TD]-$1,244.24[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For MEDICAID COMMERCIAL[/TD]
[TD]$115,651.00[/TD]
[TD]-$41,467.85[/TD]
[TD]-$57,352.41[/TD]
[TD]$498.51[/TD]
[TD]$17,329.25[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For MEDICARE[/TD]
[TD]$1,928,300.00[/TD]
[TD]-$849,877.14[/TD]
[TD]-$1,015,188.85[/TD]
[TD]$3,064.69[/TD]
[TD]$66,298.70[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For MEDICARE ADVANTAGE[/TD]
[TD]$440,932.00[/TD]
[TD]-$175,505.21[/TD]
[TD]-$210,678.60[/TD]
[TD]$2,942.08[/TD]
[TD]$57,690.27[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For MEDICARE RR[/TD]
[TD]$37,253.00[/TD]
[TD]-$15,900.45[/TD]
[TD]-$20,182.52[/TD]
[TD]$0.00[/TD]
[TD]$1,170.03[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For MOLINA[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For MULTIPLAN[/TD]
[TD]$9,156.00[/TD]
[TD]-$4,661.86[/TD]
[TD]-$1,026.14[/TD]
[TD]$0.00[/TD]
[TD]$3,468.00[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For SELF PAY[/TD]
[TD]$80,687.75[/TD]
[TD]-$38,452.48[/TD]
[TD]-$37,242.02[/TD]
[TD]$149.40[/TD]
[TD]$5,142.65[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For TRICARE[/TD]
[TD]$39,590.00[/TD]
[TD]-$18,544.17[/TD]
[TD]-$12,423.73[/TD]
[TD]$921.20[/TD]
[TD]$9,543.30[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For UNITED HEALTHCARE[/TD]
[TD]$237,266.00[/TD]
[TD]-$153,616.60[/TD]
[TD]-$65,164.26[/TD]
[TD]$1,515.56[/TD]
[TD]$20,000.70[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Totals For VETERANS ADMIN[/TD]
[TD]$23,748.00[/TD]
[TD]-$10,461.24[/TD]
[TD]-$10,419.76[/TD]
[TD]$0.00[/TD]
[TD]$2,867.00[/TD]
[/TR]
[TR]
[TD]Totals For Albuquerque[/TD]
[TD][/TD]
[TD]$3,661,122.75[/TD]
[TD]-$1,683,474.79[/TD]
[TD]-$1,780,094.21[/TD]
[TD]$16,942.84[/TD]
[TD]$214,496.59[/TD]
[/TR]
[TR]
[TD]Bellevue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]Totals For AETNA[/TD]
[TD]$504,981.00[/TD]
[TD]-$312,837.07[/TD]
[TD]-$173,143.99[/TD]
[TD]$8,490.26[/TD]
[TD]$27,490.20[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]Totals For CIGNA[/TD]
[TD]$201,832.00[/TD]
[TD]-$137,141.51[/TD]
[TD]-$43,931.99[/TD]
[TD]$546.69[/TD]
[TD]$21,305.19[/TD]
[/TR]
</tbody>[/TABLE]

 

[kenpcli]

it is working, I had another macro sub with the same name and it didn't like it. Can you tell me how to look down and column to the last row and put a single solid line at the top and a double solid line at the bottom of that last row? Or should I start a new thread.

 

[Fluff]

I had another macro sub with the same name and it didn't like it

It's best to avoid using VBA keywords as variables or Sub names, for just that reason.

For the border how about

   With Range("C" & Rows.Count).End(xlUp).Offset(, -2).Resize(, 8)
      .Borders(xlEdgeTop).LineStyle = xlContinuous
      .Borders(xlEdgeBottom).LineStyle = xlDouble
   End With

This uses Col C to find the last used row

 

[kenpcli]

Thank you, your the best.

 

[Fluff]

Glad to help & thanks for the feedback

 

[kenpcli]

How do I get to go under the last row instead of highlight the last row? Sorry I wasn't more specific.

 

[Fluff]

Simply change the first line to

With Range("C" & Rows.Count).End(xlUp).Offset([COLOR=#ff0000]1[/COLOR], -2).Resize(, 8)

 

[kenpcli]

Thank you

 

[kenpcli]

Fluff, one more question. That put borders at the very bottom of my data. But I have a 3 row between data that I need the same lines for. Can you help?