I have updated my code to tie up with your latest workbook, One thing you have done a lot on this workbook is "Merged Cells" If you are going to use VBA I strongly advise against merging cells they are very difficult to handle in VBA , use the Fromat cells "Center across selection" instead. It looks the same but causes fewer problems
The other thing I have done is to write all the additionla text into the sheet from VBA. This allows me to position the text which needs to be below the list of items flexibly to take account of how many items there are in the list. It will always appear with three blank lines after the last item.
Sub test()
With Worksheets("sheet2")
' Note I had to change this to Z from AA because you have merged cells
' Merged cells are difficult to handle in vba so: AVOID MERGING cells, use Format Center across selection instead
Lastrow = .Cells(Rows.Count, "Z").End(xlUp).Row
inarr = Range(.Cells(1, 1), .Cells(Lastrow, 35))
End With
indi = 27 ' the start row for the concatenated data
tdo = Date - 1
' note I select the worksheet so that I can see what the code is doing. lots of people might say this is unnecesary but it does make for easy debugging
Worksheets("sheet1").Select
' clear all the data from sheet1
Range("A1:Z50") = ""
Range("A1:Z50").Font.Bold = False
For i = 44 To Lastrow
If inarr(i, 30) = tdo Then
' Avoid merged cells!!!
Cells(18, 6) = inarr(i, 26) ' column W
Cells(indi, 3) = inarr(i, 4) & " " & inarr(i, 8)
indi = indi + 1
End If
Next i
Cells(17, 2) = "Ref:"
Cells(18, 2) = "Courier"
Cells(19, 2) = "FAO:"
Cells(20, 2) = "Address:"
Cells(27, 2) = "Goods"
Cells(18, 5) = "Date:"
Range(Cells(17, 2), Cells(27, 2)).Font.Bold = True
Range(Cells(18, 5), Cells(18, 5)).Font.Bold = True
Cells(indi + 3, 2) = "PLEASE REPORT ANY DAMAGES WITHIN 48HRS OF DELIVERY"
Cells(indi + 4, 2) = "ANY DAMAGES / CLAIMS AFTER WILL BE NOT APPLICABLE "
Cells(indi + 6, 2) = "Rec’d By: ……………………………………………………..………"
Cells(indi + 8, 2) = "Print Name: ……………………………………………………...……"
Cells(indi + 8, 2) = "Date: ………………………………………………………………….."
End Sub