Macro has hard code first cell, how to find next cell with data

[tynawg]

Hello,

In the link below some code is hard code referring to specific cells. I thought the source was always going to be the same column, however I just ran it a few times and found a glitch.
In this line of code

ws2.Range("A1").Value = ws1.Range("BL3").Value

BL3 was always the cell or so I thought. One instance it changed to BK3.
Same with this section , the last BK40 changed to BJ40.

 ws1.Select
    lastrow = Cells(40, "A").End(xlDown).Row

    Range("a40:a" & lastrow).Copy
    ws2.Cells(3, "a").PasteSpecial
    
    Range("p40:p" & lastrow).Copy
    ws2.Cells(3, "b").PasteSpecial
    
    Range("ac40:ac" & lastrow).Copy
    ws2.Cells(3, "c").PasteSpecial
    
    Range("al40:al" & lastrow).Copy
    ws2.Cells(3, "d").PasteSpecial
    
    Range("bk40:bk" & lastrow).Copy
    ws2.Cells(3, "e").PasteSpecial

The data is in a merged formatted work order style form and seems to always displays in the same row but perhaps the column changes.
How can I change the code to say start at say BG3 and search for the next cell with data, and copy and paste as the code specifies now and thats it then continues?
Same for row 40 there are always only 5 headings but the column may change. Can the code be written to start at A40 the look for the next cell with data 4 more times to the right and find the length of the data as this always changes. At the moment the cells are decided but when I downloaded a recent work order the last one was one column over so was not picked up.

Thanks for anyone's help and much appreciated.
Regards,
Wayne


Copy of VOID VALIDATION KCxllinestyle.xlsm

 

[tynawg]

Anyone?