Excel VBA reuseable copy and paste below last cell

quanziee

New Member
Joined
Jul 6, 2018
Messages
3
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:

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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,024
Office Version
  1. 365
Platform
  1. Windows
Cross posted on multiple sites. Please supply links to all other sites you have asked this question.

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

quanziee

New Member
Joined
Jul 6, 2018
Messages
3
I see, I wasn't aware of this. I am strapped of time and really needed help with this. Will do so.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,412
Messages
5,641,993
Members
417,250
Latest member
spr1nger

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top