Copy sheet and add Location number

K

kenpcli

Board Regular
Joined
Oct 24, 2017
Messages
129
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]
 
Sort by date Sort by votes
Fluff said:
In that case how about
Code: 
Sub FillDownLookup()

    Dim Ar As Areas
    Dim Rng As Range
    Dim ValU As Long
    
    With Sheets("Analysis")
        Set Ar = .Range("A6:A" & .Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Areas
        For Each Rng In Ar
            Rng.Value = Evaluate("VLookup(" & Rng.Offset(-1).Resize(1).Address & ",'Legend Site'!A1:B1000, 2, False)")
        Next Rng
    End With
    
End Sub
Click to expand...

Thank you I will try this and let you know how it works.
 
Upvote 0

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
kenpcli said:
Thank you I will try this and let you know how it works.
Click to expand...

This is what I get when it runs: The site number is not correct and it stops after the first location.

[TABLE="width: 704"]
<tbody>[TR]
[TD]Albuquerque[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]47[/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]47[/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]47[/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]47[/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]47[/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]47[/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]47[/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]47[/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]47[/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]47[/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]47[/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]47[/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]47[/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]47[/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]47[/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]47[/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]47[/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]47[/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]#N/A[/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]#N/A[/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]
[TR]
[TD]#N/A[/TD]
[TD]Totals For COMMERCIAL[/TD]
[TD]$314,544.00[/TD]
[TD]-$221,865.05[/TD]
[TD]-$27,915.65[/TD]
[TD]$0.00[/TD]
[TD]$64,763.30[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]Totals For COMMERCIAL FOUNDATIONS[/TD]
[TD]$11,292.00[/TD]
[TD]-$3,000.00[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[TD]$8,292.00[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]Totals For CONTRACTED COMMERCIAL[/TD]
[TD]$454.00[/TD]
[TD]-$241.25[/TD]
[TD]-$129.29[/TD]
[TD]$0.00[/TD]
[TD]$83.46[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]Totals For COORDINATED CARE[/TD]
[TD]$216,179.00[/TD]
[TD]-$93,977.41[/TD]
[TD]-$111,775.06[/TD]
[TD]$600.01[/TD]
[TD]$11,026.54[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]Totals For DSHS[/TD]
[TD]$70,023.00[/TD]
[TD]-$21,523.32[/TD]
[TD]-$38,280.68[/TD]
[TD]$0.00[/TD]
[TD]$10,219.00[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]Totals For FCHN[/TD]
[TD]$195,657.00[/TD]
[TD]-$139,056.11[/TD]
[TD]-$42,719.61[/TD]
[TD]$1,304.50[/TD]
[TD]$15,185.78[/TD]
[/TR]
[TR]
[TD]#N/A[/TD]
[TD]Totals For IPN[/TD]
[TD]$55.00[/TD]
[TD]-$20.41[/TD]
[TD]-$34.59[/TD]
[TD]$0.00[/TD]
[TD]$0.00[/TD]
[/TR]
</tbody><colgroup><col><col><col><col span="2"><col><col></colgroup>[/TABLE]
 
Upvote 0
This is what I get

Excel 2013 32 bit
AB
1SiteNo
2Albuquerque60
3Bellevue30
4Bellingham35
5Boise40
6Chehalis10
7Great Falls47
8Kennewick20
9Lewiston42
10Olympia16
11Portland18
12Silverdale22
13Spokane29
14Tacoma12
15Tualatin19
16Vancouver14
17Yakima25
Legend site




Excel 2013 32 bit
ABCDEFG
1Pacific Cataract And Laser Institute
2PCLI CONTRACTUAL YTD BY SITE
3From 1/1/2017 to 9/30/2017
410/16/2017 12:04 PM
5Chg AmtPay AmtAdj AmtRef AmtBal Amt
6Albuquerque
760Totals For AETNA$51,896.00-$31,204.59-$19,152.69$164.35$1,703.07
860Totals For BCBS$659,678.00-$320,482.96-$324,294.18$7,687.05$22,587.91
25Totals For Albuquerque$3,661,122.75-$1,683,474.79-$1,780,094.21$16,942.84$214,496.59
26Bellevue
2730Totals For AETNA$504,981.00-$312,837.07-$173,143.99$8,490.26$27,490.20
2830Totals For CIGNA$201,832.00-$137,141.51-$43,931.99$546.69$21,305.19
4930Totals For UNITED HEALTHCARE$235,518.00-$161,307.83-$60,194.42$2,498.52$16,514.27
50Totals For Bellevue$10,791,128.00-$6,031,247.18-$4,342,203.21$42,962.36$460,639.97
51Spokane
5229Totals For AETNA$51,896.00-$31,204.59-$19,152.69$164.35$1,703.07
5329Totals For BCBS$659,678.00-$320,482.96-$324,294.18$7,687.05$22,587.91
6929Totals For VETERANS ADMIN$23,748.00-$10,461.24-$10,419.76$0.00$2,867.00
70Totals For Spokane$3,661,122.75-$1,683,474.79-$1,780,094.21$16,942.84$214,496.59
71Great Falls
7247Totals For AETNA$504,981.00-$312,837.07-$173,143.99$8,490.26$27,490.20
9447Totals For UNITED HEALTHCARE$235,518.00-$161,307.83-$60,194.42$2,498.52$16,514.27
95Totals For Great Falls$10,791,128.00-$6,031,247.18-$4,342,203.21$42,962.36$460,639.97
Analysis
 
Upvote 0
Fluff said:
This is what I get
Excel 2013 32 bit
AB
SiteNo
Albuquerque
Bellevue
Bellingham
Boise
Chehalis
Great Falls
Kennewick
Lewiston
Olympia
Portland
Silverdale
Spokane
Tacoma
Tualatin
Vancouver
Yakima

<colgroup><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]60[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]30[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]35[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]40[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]47[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]20[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]42[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]16[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]22[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]29[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]19[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]14[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]25[/TD]

</tbody>
Legend site​




Excel 2013 32 bit
ABCDEFG
Pacific Cataract And Laser Institute
PCLI CONTRACTUAL YTD BY SITE
From 1/1/2017 to 9/30/2017
10/16/2017 12:04 PM
Chg AmtPay AmtAdj AmtRef AmtBal Amt
Albuquerque
Totals For AETNA$51,896.00-$31,204.59-$19,152.69$164.35$1,703.07
Totals For BCBS$659,678.00-$320,482.96-$324,294.18$7,687.05$22,587.91
Totals For Albuquerque$3,661,122.75-$1,683,474.79-$1,780,094.21$16,942.84$214,496.59
Bellevue
Totals For AETNA$504,981.00-$312,837.07-$173,143.99$8,490.26$27,490.20
Totals For CIGNA$201,832.00-$137,141.51-$43,931.99$546.69$21,305.19
Totals For UNITED HEALTHCARE$235,518.00-$161,307.83-$60,194.42$2,498.52$16,514.27
Totals For Bellevue$10,791,128.00-$6,031,247.18-$4,342,203.21$42,962.36$460,639.97
Spokane
Totals For AETNA$51,896.00-$31,204.59-$19,152.69$164.35$1,703.07
Totals For BCBS$659,678.00-$320,482.96-$324,294.18$7,687.05$22,587.91
Totals For VETERANS ADMIN$23,748.00-$10,461.24-$10,419.76$0.00$2,867.00
Totals For Spokane$3,661,122.75-$1,683,474.79-$1,780,094.21$16,942.84$214,496.59
Great Falls
Totals For AETNA$504,981.00-$312,837.07-$173,143.99$8,490.26$27,490.20
Totals For UNITED HEALTHCARE$235,518.00-$161,307.83-$60,194.42$2,498.52$16,514.27
Totals For Great Falls$10,791,128.00-$6,031,247.18-$4,342,203.21$42,962.36$460,639.97

<colgroup><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]60[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]60[/TD]

[TD="align: center"]25[/TD]

[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]49[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]50[/TD]

[TD="align: right"][/TD]

[TD="align: center"]51[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]52[/TD]
[TD="align: right"]29[/TD]

[TD="align: center"]53[/TD]
[TD="align: right"]29[/TD]

[TD="align: center"]69[/TD]
[TD="align: right"]29[/TD]

[TD="align: center"]70[/TD]

[TD="align: right"][/TD]

[TD="align: center"]71[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]72[/TD]
[TD="align: right"]47[/TD]

[TD="align: center"]94[/TD]
[TD="align: right"]47[/TD]

[TD="align: center"]95[/TD]

[TD="align: right"][/TD]

</tbody>
Analysis​
Click to expand...

ok if I try it as a stand alone it works, but if I add it to the already existing code, it breaks.

Sub MergeWorkbooks()
Dim FolderName As String
Dim directory As String, fileName As String
Dim wb1 As Workbook, wb2 As Workbook
Dim ws As Worksheet
Set wb1 = Workbooks.Add

With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder."
.AllowMultiSelect = False
.Show
On Error Resume Next
FolderName = .SelectedItems(1)
Err.Clear
On Error GoTo 0
End With
directory = FolderName & ""
fileName = Dir(directory & "*.xls?")
Do While fileName <> ""
Set wb2 = Workbooks.Open(directory & fileName)
For Each ws In wb2.Sheets
ws.Copy After:=wb1.Sheets(wb1.Sheets.Count)
Next ws
wb2.Close savechanges:=False
fileName = Dir
Loop
'Stopping Application Alerts
Application.DisplayAlerts = False
Sheets("Sheet1").Delete
'Enabling Application alerts once we are done with our task
Application.DisplayAlerts = True
Sheets("Page 1 (3)").Name = "Analysis"
Sheets("Page 1 (4)").Name = "PCLI TB"
Sheets("Page 1 (2)").Name = "Charges Posted After"
Dim Ar As Areas
Dim Rng As Range
Dim ValU As Long

With Sheets("Analysis")
Set Ar = .Range("A6:A" & .Range("C" & Rows.Count).End(xlUp).row).SpecialCells(xlBlanks).Areas
For Each Rng In Ar
Rng.Value = Evaluate("VLookup(" & Rng.Offset(-1).Resize(1).Address & ",'Legend Site'!A1:B1000, 2, False)")
Next Rng
End With
End Sub
 
Upvote 0
Do you have a sheet in that workbook called Legend Site?
 
Upvote 0
There's nothing I can see in your code that would cause a problem.
But as the code I supplied works on its own & because I can't see your files, or sheets there's not much more I can do to help.
 
Upvote 0
Fluff said:
There's nothing I can see in your code that would cause a problem.
But as the code I supplied works on its own & because I can't see your files, or sheets there's not much more I can do to help.
Click to expand...

I understand, thank you. I broke it into two macros and it works fine.
 
Upvote 0
I think it broke again, now when I run it, it doesn't like

Set Ar = .Range("A6:A" & .Range("C" & rows.Count).End(xlUp).row).SpecialCells(xlBlanks).Areas

any ideas?
 
Upvote 0
Are some of the files .xls & some newer versions such as .xlsx or .xlsm?
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Fill and Sort
2
Replies
12
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

Share this page

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