I have a spreadsheet I am look for a macros to some thing in this. I am very new to macros and this was originally written by some one else. The macro is below
The Macros is to sort the dispatch worksheet by date Newest to Oldest range A13:L38 after being updated
' it is then to put in a row between each day of the week to seperate these
' It will then copies the formulas in cell range C14:I14 and K14:L14 and paste the formula only to C14:I38 and K14:L38
' so that the formulas in cell C14:I38 And K14:L38 get repopulated into all rows
'
' Active workbooks may use different names these to be updated as names change in the code for workbook.
I would attach a picture to show the final output but do not know how.
Data is populated in a row for each delivery with the date for dispatch
This is copied form spreadsheet Column A and cell 14 is the Day below and column "C" is hidden, Column "L" is the Customer or ship.
Hope this help to give a picture for what I am needing for the final output
Cheers
NZAS
[TABLE="width: 1471"]
<colgroup><col><col><col span="6"><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]Week Commencing :- [/TD]
[TD="colspan: 5"]Monday, 5 August 2019[/TD]
[TD]Version:[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]Manifest[/TD]
[TD]Product Type[/TD]
[TD]Tonnes Req[/TD]
[TD]Lots Required[/TD]
[TD]On Truck[/TD]
[TD]Off Site[/TD]
[TD]Lots to Go[/TD]
[TD]Off Site Yard[/TD]
[TD]Colour[/TD]
[TD]Customer or Ship[/TD]
[/TR]
[TR]
[TD]Mon--Aug--05[/TD]
[TD]802082215[/TD]
[TD]178 x 6795[/TD]
[TD]300[/TD]
[TD]123[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]123[/TD]
[TD] [/TD]
[TD]RED[/TD]
[TD]Inex - Ham[/TD]
[/TR]
[TR]
[TD]Mon--Aug--05[/TD]
[TD]802082216[/TD]
[TD]202 x 7000[/TD]
[TD]300[/TD]
[TD]128[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]128[/TD]
[TD] [/TD]
[TD]BLA[/TD]
[TD]Inex - Ham[/TD]
[/TR]
[TR]
[TD]Mon--Aug--05[/TD]
[TD]802082649[/TD]
[TD]202 x 7000[/TD]
[TD]96[/TD]
[TD]41[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]26[/TD]
[TD] [/TD]
[TD]GRN / ORA[/TD]
[TD]Altus - Auck[/TD]
[/TR]
[TR]
[TD]Mon--Aug--05[/TD]
[TD]802082319[/TD]
[TD]INGOT x [/TD]
[TD]49[/TD]
[TD]48[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]48[/TD]
[TD] [/TD]
[TD]BLA / TRQ[/TD]
[TD]Navios Summer[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Tue--Aug--06[/TD]
[TD]802082215[/TD]
[TD]178 x 6795[/TD]
[TD]300[/TD]
[TD]123[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]123[/TD]
[TD] [/TD]
[TD]RED[/TD]
[TD]Inex - Ham[/TD]
[/TR]
[TR]
[TD]Tue--Aug--06[/TD]
[TD]802082216[/TD]
[TD]202 x 7000[/TD]
[TD]300[/TD]
[TD]128[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]128[/TD]
[TD] [/TD]
[TD]BLA[/TD]
[TD]Inex - Ham[/TD]
[/TR]
[TR]
[TD]Tue--Aug--06[/TD]
[TD]802082649[/TD]
[TD]202 x 7000[/TD]
[TD]96[/TD]
[TD]41[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]26[/TD]
[TD] [/TD]
[TD]GRN / ORA[/TD]
[TD]Altus - Auck[/TD]
[/TR]
[TR]
[TD]Tue--Aug--06[/TD]
[TD]802082325[/TD]
[TD]178 x 5800[/TD]
[TD]116[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD] [/TD]
[TD]RED / PIN[/TD]
[TD]Navios Summer[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Wed--Aug--07[/TD]
[TD]802082215[/TD]
[TD]178 x 6795[/TD]
[TD]300[/TD]
[TD]123[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]123[/TD]
[TD] [/TD]
[TD]RED[/TD]
[TD]Inex - Ham[/TD]
[/TR]
[TR]
[TD]Wed--Aug--07[/TD]
[TD]802082216[/TD]
[TD]202 x 7000[/TD]
[TD]300[/TD]
[TD]128[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]128[/TD]
[TD] [/TD]
[TD]BLA[/TD]
[TD]Inex - Ham[/TD]
[/TR]
[TR]
[TD]Wed--Aug--07[/TD]
[TD]802082649[/TD]
[TD]202 x 7000[/TD]
[TD]96[/TD]
[TD]41[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]26[/TD]
[TD] [/TD]
[TD]GRN / ORA[/TD]
[TD]Altus - Auck[/TD]
[/TR]
[TR]
[TD]Wed--Aug--07[/TD]
[TD]802082270[/TD]
[TD]INGOT x [/TD]
[TD]272[/TD]
[TD]264[/TD]
[TD]40[/TD]
[TD]0[/TD]
[TD]224[/TD]
[TD] [/TD]
[TD]BLA[/TD]
[TD]Navios Summer[/TD]
[/TR]
</tbody>[/TABLE]
The macro I did have does not do all that is required as I do not want to have the data to extend past row 38 and only insert the rows to fit no wider than column "L"
The Macros is to sort the dispatch worksheet by date Newest to Oldest range A13:L38 after being updated
' it is then to put in a row between each day of the week to seperate these
' It will then copies the formulas in cell range C14:I14 and K14:L14 and paste the formula only to C14:I38 and K14:L38
' so that the formulas in cell C14:I38 And K14:L38 get repopulated into all rows
'
' Active workbooks may use different names these to be updated as names change in the code for workbook.
I would attach a picture to show the final output but do not know how.
Data is populated in a row for each delivery with the date for dispatch
This is copied form spreadsheet Column A and cell 14 is the Day below and column "C" is hidden, Column "L" is the Customer or ship.
Hope this help to give a picture for what I am needing for the final output
Cheers
NZAS
[TABLE="width: 1471"]
<colgroup><col><col><col span="6"><col><col><col></colgroup><tbody>[TR]
[TD="colspan: 3"]Week Commencing :- [/TD]
[TD="colspan: 5"]Monday, 5 August 2019[/TD]
[TD]Version:[/TD]
[TD]1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]Manifest[/TD]
[TD]Product Type[/TD]
[TD]Tonnes Req[/TD]
[TD]Lots Required[/TD]
[TD]On Truck[/TD]
[TD]Off Site[/TD]
[TD]Lots to Go[/TD]
[TD]Off Site Yard[/TD]
[TD]Colour[/TD]
[TD]Customer or Ship[/TD]
[/TR]
[TR]
[TD]Mon--Aug--05[/TD]
[TD]802082215[/TD]
[TD]178 x 6795[/TD]
[TD]300[/TD]
[TD]123[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]123[/TD]
[TD] [/TD]
[TD]RED[/TD]
[TD]Inex - Ham[/TD]
[/TR]
[TR]
[TD]Mon--Aug--05[/TD]
[TD]802082216[/TD]
[TD]202 x 7000[/TD]
[TD]300[/TD]
[TD]128[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]128[/TD]
[TD] [/TD]
[TD]BLA[/TD]
[TD]Inex - Ham[/TD]
[/TR]
[TR]
[TD]Mon--Aug--05[/TD]
[TD]802082649[/TD]
[TD]202 x 7000[/TD]
[TD]96[/TD]
[TD]41[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]26[/TD]
[TD] [/TD]
[TD]GRN / ORA[/TD]
[TD]Altus - Auck[/TD]
[/TR]
[TR]
[TD]Mon--Aug--05[/TD]
[TD]802082319[/TD]
[TD]INGOT x [/TD]
[TD]49[/TD]
[TD]48[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]48[/TD]
[TD] [/TD]
[TD]BLA / TRQ[/TD]
[TD]Navios Summer[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Tue--Aug--06[/TD]
[TD]802082215[/TD]
[TD]178 x 6795[/TD]
[TD]300[/TD]
[TD]123[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]123[/TD]
[TD] [/TD]
[TD]RED[/TD]
[TD]Inex - Ham[/TD]
[/TR]
[TR]
[TD]Tue--Aug--06[/TD]
[TD]802082216[/TD]
[TD]202 x 7000[/TD]
[TD]300[/TD]
[TD]128[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]128[/TD]
[TD] [/TD]
[TD]BLA[/TD]
[TD]Inex - Ham[/TD]
[/TR]
[TR]
[TD]Tue--Aug--06[/TD]
[TD]802082649[/TD]
[TD]202 x 7000[/TD]
[TD]96[/TD]
[TD]41[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]26[/TD]
[TD] [/TD]
[TD]GRN / ORA[/TD]
[TD]Altus - Auck[/TD]
[/TR]
[TR]
[TD]Tue--Aug--06[/TD]
[TD]802082325[/TD]
[TD]178 x 5800[/TD]
[TD]116[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD] [/TD]
[TD]RED / PIN[/TD]
[TD]Navios Summer[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Wed--Aug--07[/TD]
[TD]802082215[/TD]
[TD]178 x 6795[/TD]
[TD]300[/TD]
[TD]123[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]123[/TD]
[TD] [/TD]
[TD]RED[/TD]
[TD]Inex - Ham[/TD]
[/TR]
[TR]
[TD]Wed--Aug--07[/TD]
[TD]802082216[/TD]
[TD]202 x 7000[/TD]
[TD]300[/TD]
[TD]128[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]128[/TD]
[TD] [/TD]
[TD]BLA[/TD]
[TD]Inex - Ham[/TD]
[/TR]
[TR]
[TD]Wed--Aug--07[/TD]
[TD]802082649[/TD]
[TD]202 x 7000[/TD]
[TD]96[/TD]
[TD]41[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]26[/TD]
[TD] [/TD]
[TD]GRN / ORA[/TD]
[TD]Altus - Auck[/TD]
[/TR]
[TR]
[TD]Wed--Aug--07[/TD]
[TD]802082270[/TD]
[TD]INGOT x [/TD]
[TD]272[/TD]
[TD]264[/TD]
[TD]40[/TD]
[TD]0[/TD]
[TD]224[/TD]
[TD] [/TD]
[TD]BLA[/TD]
[TD]Navios Summer[/TD]
[/TR]
</tbody>[/TABLE]
The macro I did have does not do all that is required as I do not want to have the data to extend past row 38 and only insert the rows to fit no wider than column "L"
Code:
Range("A14:I38").Select
ActiveWorkbook.Worksheets("This Week").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("This Week").Sort.SortFields.Add Key:=Range( _
"A14:A43"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("This Week").Sort
.SetRange Range("A13:j38")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A14").Select
With Sheets("This Week")
.Range("a14").End(xlDown).Select
While ActiveCell.Row <> 14
If Day(ActiveCell.Value) = Day(ActiveCell.Offset(-1, 0)) Then
ActiveCell.Offset(-1, 0).Select
Else
ActiveCell.EntireRow.Select
Selection.Insert Shift:=xlDown
.Range("e14:J14").Copy
ActiveCell.Offset(0, 4).PasteSpecial (xlFormulas)
.Range("44:44").EntireRow.Delete
ActiveCell.Offset(-1, -4).Select
End If
Wend
End With
End Sub
Last edited by a moderator: