ExcelNewbie2020
Active Member
- Joined
- Dec 3, 2020
- Messages
- 322
- Office Version
- 365
- Platform
- Windows
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
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