Hi, complete beginner with excel VBA here. I have a list of people and their information on columns "A:D". Column "A" consists of the persons first name, "B" consists of their last name, "C" consists of their gender, and "D" consists of their rank. The list starts from "A10" and continues down. However, the number of people on this list is not a constant. I want to copy this information onto different cells and will begin from Row 10. Information from columns "A:B" will go to columns "N:AB", information from column "C" will go to column "AC:AE", information from column "D" will go to columns "AF:AH". Only the cells of the destination for the pasted information are merged, from "N:AB", "AC:AE", and "AF:AH". I also want to make it such that once the user has run the macro and pasted the information, they will be able to delete the list from columns "A:D", not needed in the code, and be able to fill in new information for another group of people and when they run the macro again, this information will be pasted directly below the information of the previous group, so this macro can be reused.
I would like to know if what I'm trying to do is even possible, and if it isn't some suggestions to be able to perform this task will be appreciated.
I tried to write my own code from my current limited understanding of VBA but it doesn't work and I'm at the point where I don't even know what i'm doing anymore. Here is my current code:
Some comments on the answer codes would be really helpful since I just only started learning VBA. Feel free to ask any questions if my question is confusing.
I would like to know if what I'm trying to do is even possible, and if it isn't some suggestions to be able to perform this task will be appreciated.
I tried to write my own code from my current limited understanding of VBA but it doesn't work and I'm at the point where I don't even know what i'm doing anymore. Here is my current code:
Code:
Sub CopyandPasteCabinCrew()
Dim NoOfCrew As Long
Dim LastRow As Long
NoOfCrew = Sheets("Hotel Booking").Cells(Rows.Count, "A").End(xlUp).Row
NoOfCrew = NoOfCrew + 1
LastRow = WorksheetFunction.Max(Sheets("Hotel Booking").Cells(Rows.Count, "N").End(xlUp).Row, 9)
LastRow = LastRow + 1
ActiveSheet.Range("N:AB" & LastRow).Value = ActiveSheet.Range("A10:B" & NoOfCrew).Value
ActiveSheet.Range("AC:AE" & LastRow).Value = ActiveSheet.Range("C10" & NoOfCrew).Value
ActiveSheet.Range("AF:AH" & LastRow).Value = ActiveSheet.Range("D10" & NoOfCrew).Value
End SubSome comments on the answer codes would be really helpful since I just only started learning VBA. Feel free to ask any questions if my question is confusing.
