PDA

View Full Version : Excel VBA reuseable copy and paste below last cell



quanziee
07-06-2018, 08:33 AM
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.

22528

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
07-06-2018, 01:15 PM
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
07-06-2018, 01:41 PM
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
07-06-2018, 10:43 PM
Hey, here is my file. I'll try your code and see if it works. Thanks for the help!

quanziee
07-06-2018, 11:36 PM
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
07-07-2018, 09:22 AM
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
07-08-2018, 08:07 AM
I also asked the same question in these sites:
https://stackoverflow.com/questions/51208522/excel-vba-reuseable-copy-and-paste-below-last-cell?noredirect=1#comment89398854_51208522
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1205206-excel-vba-reuseable-copy-and-paste-below-last-cell
https://www.mrexcel.com/forum/excel-questions/1062168-excel-vba-reuseable-copy-paste-below-last-cell.html

mattreingold
07-09-2018, 05:20 AM
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
07-09-2018, 05:23 AM
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
07-09-2018, 10:02 AM
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/help-forums/excel-vba-macros/1205206-excel-vba-reuseable-copy-and-paste-below-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:
22539