VBA to replicate values to allign with the longest non empty cell in other columns

[ExcelNewbie2020]

the below script transfer certain values from sheet1 to sheet 2.. i need to modify it to replicate the values in cell Z4,G29,O27,AA27,D27, and C51 so it will be alligned with the the non empty cell in range column B,C,D. Please see photo

Sub TransferData()
Dim wsStaff As Worksheet
Dim wsGuest As Worksheet
Dim lastRow As Long

' Set references to the worksheets
Set wsStaff = ThisWorkbook.Sheets("sHEET1")
Set wsGuest = ThisWorkbook.Sheets("SHEET2")

' Find the last row in the SHEET2
lastRow = wsGuest.Cells(wsGuest.Rows.Count, "A").End(xlUp).Row + 1

' Transfer data
wsGuest.Cells(lastRow, "A").Value = wsStaff.Range("Z4").Value
wsGuest.Cells(lastRow, "B").Resize(15).Value = wsStaff.Range("B11:B25").Value
wsGuest.Cells(lastRow, "C").Resize(15).Value = wsStaff.Range("V11:V25").Value
wsGuest.Cells(lastRow, "D").Resize(15).Value = wsStaff.Range("AB11:AB25").Value
wsGuest.Cells(lastRow, "E").Value = wsStaff.Range("G29").Value
wsGuest.Cells(lastRow, "F").Value = wsStaff.Range("O27").Value
wsGuest.Cells(lastRow, "G").Value = wsStaff.Range("AA27").Value
wsGuest.Cells(lastRow, "H").Value = wsStaff.Range("D27").Value
wsGuest.Cells(lastRow, "I").Value = wsStaff.Range("C51").Value
End Sub

 

[Eric W]

Just change

wsGuest.Cells(lastRow, "A").Value = wsStaff.Range("Z4").Value

to

wsGuest.Cells(lastRow, "A").Resize(15).Value = wsStaff.Range("Z4").Value

Repeat for the E to I lines as well.