VBA loop to split up data based on count and week

[Earlyfreak]

I am looking for a direction to automate this manual intense process. I have different counts to do each week example
Week 5
53
Week 6
30
Week 7
51
Week 8
55
Week 9
51
Week 10
60
I am looking to take the week total and split it into 5 workdays to complete
The problem is each week is different. I manually split and process now.
I can do this with formula's that I know of
Is there a VBA solution?
Any direction would be greatly appriciated

Here is example data
[TABLE="width: 567"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Item [/TD]
[TD]Description[/TD]
[TD]Week #[/TD]
[TD]Completion Date[/TD]
[/TR]
[TR]
[TD="align: right"]711[/TD]
[TD]N 50 GAL 40 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]710[/TD]
[TD]N 40 GAL 40 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]709[/TD]
[TD]N 40 GAL 40 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]706[/TD]
[TD]-1NCWW 50 GAL 240V[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]712[/TD]
[TD]N 48 GAL 40 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]691[/TD]
[TD]N 75 GAL 76 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]774[/TD]
[TD]S6N 50 GAL 40 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]702[/TD]
[TD]-1NCWW 40 GAL 240V[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]705[/TD]
[TD]-1NCWW 40 GAL 240V[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]557[/TD]
[TD]-1NCZZ 208V / 4500[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]471[/TD]
[TD]1NAL RESIDENTIAL ELECTRIC[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]728[/TD]
[TD]T6N 50 GAL 40 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]215[/TD]
[TD]0N 50 GAL 40 BTU NAT 10YR[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]747[/TD]
[TD]H6N 75 GAL 76 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]703[/TD]
[TD]-1NCWW 50 GAL 240V[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]704[/TD]
[TD]-1NCWW 30 GAL 240V[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]690[/TD]
[TD]N 48 GAL 65 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]169[/TD]
[TD]00 BW 240V-4500W STD ELMT[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]469[/TD]
[TD]-1NAL RESIDENTIAL[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]219[/TD]
[TD]0N 40 GAL 40 BTU NAT 10YR[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]465[/TD]
[TD]-1NAL 10 GAL ELECT HEATER[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]759[/TD]
[TD]-1NCWW 38 GAL 240V[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]758[/TD]
[TD]-1NCWW 28 GAL 240V[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]892[/TD]
[TD]0N 75 GAL 76 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]726[/TD]
[TD]T6N 40GAL 40 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]695[/TD]
[TD]5H6N 75 GAL 80 BTU NAT[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]713[/TD]
[TD]S6N 40 GAL 40 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]732[/TD]
[TD]0H6N 48 GAL 60 BTU NAT[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]767[/TD]
[TD]X 40 GAL 38 BTU LP GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]760[/TD]
[TD]-1NCWW 47 GAL 240V[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]470[/TD]
[TD]-1NAL RESIDENTIAL ELECTR[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]812[/TD]
[TD]LVL 1 UPGRD HTR KIT[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]707[/TD]
[TD]N 30 GAL 32 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]765[/TD]
[TD]X 50 GAL 36 BTU LP GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]298[/TD]
[TD]01 BW KIT-NAT GAS VALVE[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]779[/TD]
[TD]T6X 50 GAL 40 BTU LP GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]681[/TD]
[TD]R-L 40 GAL INDIRECT[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]764[/TD]
[TD]X 40 GAL 36 BTU LP GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]895[/TD]
[TD]N 40 GAL 34 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]701[/TD]
[TD]N 50 GAL 50 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]777[/TD]
[TD]S6X 50 GAL 38 BTU LP GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]292[/TD]
[TD]00 BW VAPOR SENSOR[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]782[/TD]
[TD]H6X 75 GAL 75.5 BTU LP[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]734[/TD]
[TD]00 BW VENT TERMINAL /[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]746[/TD]
[TD]0S6N 50 GAL 40 BTU NAT[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]496[/TD]
[TD]763N 75 GAL 76 BTU NAT[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]730[/TD]
[TD]H6N 48 GAL 65 BTU NAT GAS[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]313[/TD]
[TD]05 BW PILOT-NAT 17/26 HON[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]220[/TD]
[TD]0N 48 GAL 40 BTU NAT 10YR[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]693[/TD]
[TD]00 UPPER&LOWER T STAT[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]221[/TD]
[TD]T 10GAL 240V/4KW 208V/3KW[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]680[/TD]
[TD]R-L 50 GAL INDIRECT[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD="align: right"]734[/TD]
[TD]0S6N 40 GAL 40 BTU NAT[/TD]
[TD]Week 5[/TD]
[TD]May 19th[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]864[/TD]
[TD]V PILOT *** Y 190 NAT RK[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]958[/TD]
[TD]TI-540H NG TANKLESS HTR[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]692[/TD]
[TD]6005 KIT PILOT WITH[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]696[/TD]
[TD]4005 KIT GAS CONTROL[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]53[/TD]
[TD]TUBING 560-742-860[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]541[/TD]
[TD]631 NEUTRALIZER KIT[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]89[/TD]
[TD]C THERM ROLLOUT SWITCH[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]544[/TD]
[TD]620 MAINTENANCE KIT F/[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]379[/TD]
[TD]ON ELECTRODE KIT F/ULTRA[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD]SWITCH 24V 510-300-013[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]90[/TD]
[TD] ASSY W/CABLE 511-330-218[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]131[/TD]
[TD]TRIM EG-PIDN 381-800-837[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]836[/TD]
[TD]N CGA-4-PIDN GAS BLR 2012[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]837[/TD]
[TD]N CGA-5-PIDN GAS BLR 2012[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]893[/TD]
[TD]N EG30-50 GAS CTRL 2012[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]542[/TD]
[TD]630 ULTRA P/T GAUGE[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]32[/TD]
[TD]ECKETT BURNER[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]27[/TD]
[TD]ERMOSTAT RES 633-900-130[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]898[/TD]
[TD]N EG40-45 JACKET 2012[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]58[/TD]
[TD]HOOD EG-45 450-021-258[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]55[/TD]
[TD]HOOD EG-40 450-021-257[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]24[/TD]
[TD]R KIT 511-330-148[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]946[/TD]
[TD]0-020 THERMOSTAT RPR KIT[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]39[/TD]
[TD]N ASSY EG 40-45[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]56[/TD]
[TD]N RELAY 24V 510-350-223[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]888[/TD]
[TD]N EG45 BASE ASSY 2012[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]172[/TD]
[TD]0-658 U-CONTROL F/ULTRA[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]65[/TD]
[TD]RELAY W/RECP. 510-312-166[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]33[/TD]
[TD]ECKETT BURNER[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
[TR]
[TD="align: right"]835[/TD]
[TD]N CGA-3PIDN GAS BLR 2012[/TD]
[TD]Week 6[/TD]
[TD]May 26th[/TD]
[/TR]
</tbody>[/TABLE]

 

[mse330]

I am not sure I have understood your issue correctly but the below code will split each week data by inserting a row between them. If you could show us a sample of the output that you need perhaps we can assist you more.

Assuming your data starts in A1

Sub Insert_Row()

Dim lRow As Double
lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For x = lRow To 3 Step -1
    If Cells(x, 3).Value <> Cells(x, 3).Offset(-1, 0).Value Then
        Rows(x).EntireRow.Insert
    End If
Next x

End Sub

 

[Earlyfreak]

Thanks mse330

The loop is a start I wanted to loop through every 5 items, then assign the first day Monday to those 5 items then
The next 5 to Tues etc then on the next week start over. Do not know if VBA can do this.

I ended up running a pivot the copy values and formatting not ideal but quicker then formula's

I looking to split like this

[TABLE="width: 692"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Completion Date[/TD]
[TD]Week #[/TD]
[TD]Day[/TD]
[TD]Item [/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]19-May[/TD]
[TD]Week 5[/TD]
[TD]Monday[/TD]
[TD]311298[/TD]
[TD]239-47463-01 BW KIT-NAT GAS VALVE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]640701[/TD]
[TD]BW RG250S6N 50 GAL 50 BTU NAT GAS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]640703[/TD]
[TD]BW RE350S6-1NCWW 50 GAL 240V[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]640712[/TD]
[TD]BW RG250L6N 48 GAL 40 BTU NAT GAS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]640726[/TD]
[TD]BW RG2PV40T6N 40GAL 40 BTU NAT GAS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]640746[/TD]
[TD]BW RG2PDV50S6N 50 GAL 40 BTU NAT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]640760[/TD]
[TD]BW RE250L6-1NCWW 47 GAL 240V[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]654693[/TD]
[TD]265-51046-00 UPPER&LOWER T STAT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]687219[/TD]
[TD]BW RG240T10N 40 GAL 40 BTU NAT 10YR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]739557[/TD]
[TD]BW RE250L6-1NCZZ 208V / 4500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Tuesday[/TD]
[TD]640691[/TD]
[TD]BW RG275H6N 75 GAL 76 BTU NAT GAS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]640704[/TD]
[TD]BW RE330S6-1NCWW 30 GAL 240V[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]640711[/TD]
[TD]BW RG250T6N 50 GAL 40 BTU NAT GAS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]640777[/TD]
[TD]BW RG1PV50S6X 50 GAL 38 BTU LP GAS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]640779[/TD]
[TD]BW RG2PV50T6X 50 GAL 40 BTU LP GAS[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]642465[/TD]
[TD]BW RE110U6-1NAL 10 GAL ELECT HEATER[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]642470[/TD]
[TD]BW RE1-2U6-1NAL RESIDENTIAL ELECTR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]642471[/TD]
[TD]BW RE16U6-1NAL RESIDENTIAL ELECTRIC[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]642695[/TD]
[TD]BW RG2PDV75H6N 75 GAL 80 BTU NAT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]643496[/TD]
[TD]B/W LG275H763N 75 GAL 76 BTU NAT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]684221[/TD]
[TD]LE110U31NCT 10GAL 240V/4KW 208V/3KW[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Wednesday[/TD]
[TD]297812[/TD]
[TD]BW SINGLE LVL 1 UPGRD HTR KIT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]311292[/TD]
[TD]239-45560-00 BW VAPOR SENSOR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]640680[/TD]
[TD]BW SW-2-50R-L 50 GAL INDIRECT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]640681[/TD]
[TD]BW SW-2-40R-L 40 GAL INDIRECT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]640690[/TD]
[TD]BW RG250H6N 48 GAL 65 BTU NAT GAS


[/TD]
[/TR]
</tbody>[/TABLE]

 

[mse330]

You are welcome ... I put the below code which will basically insert the day next to the first item starting from Monday then will skip 5 items & goes to the 6th item & insert Tuesday ... etc.. skipping the weekends of Friday & Saturday. Again assuming your data start in cell A1

Try the below code & let me know if this is what you are looking for

Sub Split()

Dim lRow As Long, dDate As Date
lRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
dDate = #1/1/2018#
Cells(1, 5).Value = "Day"

For x = 2 To lRow Step 5
    Cells(x, 5).Value = Format(dDate, "DDDD")
    dDate = WorksheetFunction.WorkDay_Intl(dDate, 1)
Next x

End Sub

 

[mse330]

If you wish to have the day filled in each row, you can use the below code

Sub Split_Fill()

Dim lRow As Long, dDate As Date
lRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
dDate = #1/1/2018#

For x = 1 To lRow - 1
    Cells(x, 5).Value = Format(dDate, "DDDD")
    If x Mod 5 = 0 Then dDate = WorksheetFunction.WorkDay_Intl(dDate, 1)
Next x

Cells(1, 5).Insert Shift:=xlDown
Cells(1, 5).Value = "Day"

End Sub

 

[Earlyfreak]

Thanks again mse330

That works, pretty slick. Tried both and even added

Rows(x).EntireRow.Insert

Between form your early example

final

Sub Split()
Dim lRow As Long, dDate As Date
lRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
dDate = #1/1/2018#
Cells(1, 5).Value = "Day"
For x = 2 To lRow Step 6
    Cells(x, 5).Value = Format(dDate, "DDDD")
    dDate = WorksheetFunction.WorkDay_Intl(dDate, 1)
    Rows(x).EntireRow.Insert
Next x
End Sub

​

Each week has different amounts of counts which divide into the 5 work day differently. Is there a way to count the week no total and divide the split number equaly among the 5 days?

Thanks for getting me the right direction. Much appreciated

​

 

[mse330]

Glad I could help ... It is much easier for me if you can you show me example of the desired output

 

[Earlyfreak]

I do not see where I can post an Attachment to show you?