Excel VBA reuseable copy and paste below last cell

[quanziee]

Hi, complete beginner with excel VBA here. I have a list of people and their information on columns "A: D". 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". 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.

Screenshot (2).jpg

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:

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 Sub

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.

[mattreingold]

Quanzieee, I would be more than happy to help you. For me, at least, your picture is unreadable. If you could upload this workbook, or a dummy workbook with just the outlines and templates that would suffice.

I will be able to build you a solution rather quickly. Looking forward to your response!

[mattreingold]

I tried my best squint and pieced this together, not sure exactly which row you wanted to start pasting the values so I guessed. Let me know if you need anything tweaked!

Sub DataTransfer()
    Dim DataTransfer As Workbook
    Set DataTransfer = ThisWorkbook
    Dim TableSheet As Worksheet
    Set TableSheet = DataTransfer.Sheets(1)
    Dim firstName(), lastName(), gender(), Rank()
    Dim firstRow As Integer, lastRow As Integer
    
    ' Hardcoded - row after header
    firstRow = 10
    
    ' Gets last row of people in row A
    lastRow = TableSheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Index checks to see where previous row of table is (if macro has ran before
    tableRowChecker = TableSheet.Cells(Rows.Count, "N").End(xlUp).Row
    
    ' Gets values and stores into arrays
    With TableSheet
        firstName = Range(.Cells(firstRow, 1), .Cells(lastRow, 1))
        lastName = Range(.Cells(firstRow, 2), .Cells(lastRow, 2))
        gender = Range(.Cells(firstRow, 3), .Cells(lastRow, 3))
        Rank = Range(.Cells(firstRow, 4), .Cells(lastRow, 4))
    End With
    
    ' Pastes values into table based on what 'tableRowChecker' found was the previous row
    For i = LBound(firstName) To UBound(lastName)
        TableSheet.Cells(i + tableRowChecker, 14).Value = firstName(i, 1) & " " & lastName(i, 1)
        TableSheet.Cells(i + tableRowChecker, 29).Value = gender(i, 1)
        TableSheet.Cells(i + tableRowChecker, 32).Value = Rank(i, 1)
    Next i
    
    ' Clears range after use
    TableSheet.Range("A10:D" & lastRow).ClearContents
    
End Sub

[quanziee]

Hey, here is my file. I'll try your code and see if it works. Thanks for the help!

[quanziee]

Hey, your code works perfectly! Thanks so much! I have one more thing regarding this though. What if I want to enter for another set of crew below the cabin crew and want it to function the exact same way? The user would still have to enter the information of the list of the other crew, the tech crew, in the same columns "A: D". So once the user has already filled in for the cabin crew, they can fill in for the tech crew too. You can see what I mean in my file.

I tried creating another macro to transfer the data for the tech crew, and it worked but when I tried to run the macro for the cabin crew, it came up with an error saying "type mismatch".

[quanziee]

I'm sorry, it only says "type mismatch" when I only fill in information for one person. I have also found a problem when I was playing around with the code. Sometimes, the information will get transferred to cells that are way below where they are supposed to be. I'm not exactly sure about the root of this problem.

[quanziee]

I also asked the same question in these sites:
https://stackoverflow.com/questions/...98854_51208522
https://www.ozgrid.com/forum/forum/h...elow-last-cell
https://www.mrexcel.com/forum/excel-...last-cell.html

[mattreingold]

Quanziee, thanks for the inclusion of the cross-post warning.

According to the workbook you linked, this code should work: (didn't know where you wanted the passport No.'s posted)

Sub CopyAndPasteCabinCrewActualArray()


Dim firstRow As Integer, StartAt As Integer, i As Integer
Dim NoOfCrew As Long
Dim firstNameArray(), lastNameArray(), genderArray(), RankArray(), passportNoArray()
Dim HotelBooking As Worksheet
Set HotelBooking = ThisWorkbook.Worksheets("Hotel Booking")


firstRow = 10


NoOfCrew = 0
NoOfCrew = HotelBooking.Cells(Rows.Count, "A").End(xlUp).Row
NoOfCrew = NoOfCrew - 9
StartAt = firstRow - 1


If NoOfCrew = 1 Then
    Dim firstName As String, lastName As String, gender As String, Rank As String, passportNo As String
    With HotelBooking
        firstName = .Cells(firstRow, 1)
        lastName = .Cells(firstRow, 2)
        gender = .Cells(firstRow, 3)
        Rank = .Cells(firstRow, 4)
        passportNo = .Cells(firstRow, 5)
        
        HotelBooking.Cells(1 + StartAt, 14).Value = firstName & " " & lastName
        HotelBooking.Cells(1 + StartAt, 29).Value = gender
        HotelBooking.Cells(1 + StartAt, 32).Value = Rank
        HotelBooking.Cells(1 + StartAt, 49).Value = passportNo
    End With
Else
    With HotelBooking
        firstNameArray = Range(.Cells(firstRow, 1), .Cells(NoOfCrew + 9, 1))
        lastNameArray = Range(.Cells(firstRow, 2), .Cells(NoOfCrew + 9, 2))
        genderArray = Range(.Cells(firstRow, 3), .Cells(NoOfCrew + 9, 3))
        RankArray = Range(.Cells(firstRow, 4), .Cells(NoOfCrew + 9, 4))
        passportNoArray = Range(.Cells(firstRow, 5), .Cells(NoOfCrew + 9, 5))
    End With
    
    For i = LBound(firstNameArray) To UBound(lastNameArray)
        HotelBooking.Cells(i + StartAt, 14).Value = firstNameArray(i, 1) & " " & lastNameArray(i, 1)
        HotelBooking.Cells(i + StartAt, 29).Value = genderArray(i, 1)
        HotelBooking.Cells(i + StartAt, 32).Value = RankArray(i, 1)
        HotelBooking.Cells(i + StartAt, 49).Value = passportNoArray(i, 1)
    Next i
End If


End Sub

It is by far best practice to dimension your variables (especially workbooks and sheets) before use, thus I set a variable for hotelbooking and used it throughout the code.

If you could attach pictures to when the information gets pasted "Way below" I could help you there.

This should also help with the 'one person' error you were getting.

[mattreingold]

Excuse me, theres actually a slight error in the code I just posted.

The 'StartAt' statement should read:

StartAt = HotelBooking.Cells(Rows.Count, "N").End(xlUp).Row

[quanziee]

Hey thanks for the help. So sorry for all the trouble caused, but the file I posted here was incomplete at the time and my project mate had sent it to me to play around with it. The final one was recently sent to me and the format of the worksheet has been changed a bit.

You can view the thread here:

https://www.ozgrid.com/forum/forum/h...elow-last-cell

I tried to improvise your code for it to work in the new worksheet but I ran into some problems.

Here is my current code for the new file:

Sub TransferData()

Dim firstRow As Integer, StartAt As Integer, i As Integer
Dim NoOfCrew As Long
Dim firstName(), lastName(), gender(), Rank(), passportNo()




firstRow = 13


NoOfCrew = Sheets("Hotel Booking").Cells(Rows.count, "B").End(xlUp).Row
NoOfCrew = NoOfCrew - 13


StartAt = WorksheetFunction.Max(Sheets("Hotel Booking").Cells(Rows.count, "AB").End(xlUp).Row, 9)


  With Sheets("Hotel Booking")
     firstName = Range(.Cells(firstRow, 2), .Cells(NoOfCrew, 2))
     lastName = Range(.Cells(firstRow, 5), .Cells(NoOfCrew, 5))
     gender = Range(.Cells(firstRow, 7), .Cells(NoOfCrew, 7))
     Rank = Range(.Cells(firstRow, 8), .Cells(NoOfCrew, 8))
     passportNo = Range(.Cells(firstRow, 9), .Cells(NoOfCrew, 9))
    
    
  End With


   For i = LBound(firstName) To UBound(lastName)
      
      Sheets("Hotel Booking").Cells(i + StartAt, 28).Value = firstName(i, 1) & " " & lastName(i, 1)
      Sheets("Hotel Booking").Cells(i + StartAt, 43).Value = gender(i, 1)
      Sheets("Hotel Booking").Cells(i + StartAt, 46).Value = Rank(i, 1)
      Sheets("Hotel Booking").Cells(i + StartAt, 49).Value = passportNo(i, 1)
      
   Next i




End Sub

Even though my problem has already been solved, I would like to know why my code is not working and what the fix to it is. At least I could learn from my mistakes this way and I also understand your code better than the solution in the link above. Thanks again and so sorry for the inconvenience caused.

Attached is the new file:
Hotel Booking Sample.xlsm