Macro to cut and paste data in a dropdown

[LEEDA12345]

Hi

I really need some help in making a Macro to do the following:

The user selects a product from a drop down I need the macro to lookup the selected student in the dropdown in a table on another worksheet. Once found cut the whole row this that student in and paste it in another worksheet.

[daffodil11]

A few questions:

1. Paste it to where in a worksheet?
2. Do you want to paste over every record each time?
3. Do you want to create another worksheet with each paste?
4. What is the address of the dropdown menu?
5. What is the range of the table you are looking it up on?

[LEEDA12345]

I am trying to upload a workbook but its not working
the dropdown is in shhe1 A1 sheet then the table im looking up is from A-d rows 1-10 sheet 2 I want the row to be cut then pasted onto sheet 3 each time it is pasted into a new row

[daffodil11]

You've said Cut and Paste both times, and not Copy and Paste.

Removing rows of data from your lookup may cause it to fail after the first grab. Is this what you want to do?

[LEEDA12345]

sorry what to you mean be fail? it cutting the rows will be a problem is it possible to copy the information to another worksheet then the rows which we copied from turn blank sorry if im being confusing

[LEEDA12345]

it want it to cut the information in the rows

[daffodil11]

How about like this?

cut and paste lookup.xlsm

[LEEDA12345]

Yes that's so good I just have some questions how do I view the code for it so I can apply it to my work and is it possible it could be assigned to a button

[LEEDA12345]

Thank you for your help and time btw

[daffodil11]

Sure, anything can be assigned to a button.

You can reach the Visual Basic editor by hitting Alt+F11.

You can also add it to your toolbar by going to File -> Options -> Customize Ribbon and checking the box in right column for Developer. It's a whole other tab it adds to your Excel, which is turned off by default.

In the VB editor, you'll see your sheets down the left side in a little tree. Every sheet can have its own code, as can things called Modules which are sort of like generic code places. I think. (I only started teaching myself this stuff a couple week ago.)

You can freely close the editor window without saving. It's part of the workbook, closing it just "hides" that information which always exists alongside every workbook.

Anywho, sheet1 has a special piece of code called a Worksheet_Change_Event. This kicks off everytime the parameters are met. In this case, I've targeted the dropdown in that sheet. If you want to assign it to a button, you'll probably want to remove all of that code because right now it's happening automatically every time you select something from the dropdown.


The actual copy and paste code is in Module1. Double click it to open it up. Here you'll see:

Please Login or Register  to view this content.

This subroutine/macro is called Bangarang. I give all my macros funny names. You can change the word Bangarang to anything you want.

Dim means dimension, and it's our way to declare what kind of variable each is. It's not absolutely necessary, but when you're writing the code the editor will help you by suggesting things different types of variables can do, and also how to handle errors.

x is your drop down range. Adjust this to whatever you need for your purposes.

c's range is the first column of your lookup. Adjust this for your lookup table.

Destination:= Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1) - this is where we are copying to. the rest of that line is autodetecting where the last entry is and then pasting under it.

The code states "for each cell in A1:A10, if the cell = x, cut that whole row, copy it to Sheet3 wherever the first blank row in A is"

Technically, if you had 3 rows on your lookup that matched the dropdown, it would cut them all and paste them to Sheet3. So... don't have duplicates


If you want to add a button, make sure you remove the code from Sheet1 first to stop it from happening automatically. Then go to the Developer Tab and choose Insert and the object in the upper left corner. Draw a rectangle. Right click on it, and assign Macro and choose Bangarang.

If you don't like that button, you can also go to the Insert Tab, choose Shape, and draw something more appealing. That shape can also be right-clicked and assigned a macro.

[LEEDA12345]

Ok tank you buddy I will try it as soon as I have time if I get stuck or have any problems do you might if I can message you please?

[daffodil11]

Go right ahead. This is my second home.

[LEEDA12345]

Is it possible instead of the thing I have selected in the drop dispersing (as it goes blanks) it remains however it will turn red if it has been looked up cut and then passed on to the sheet?

[daffodil11]

The dropdown will always be in black and white.

You do want to put a special character in front/back of the name instead after it's used?

[LEEDA12345]

instead could I apply a message box so if the user clicks the thing which has be cut and pasted it will say sorry this this already been extracted

[LEEDA12345]

this way it would avoid leaving some things in the dropdown blank

[daffodil11]

That was a lot harder!

We change the macro to:

Please Login or Register  to view this content.

I created a new tab: ListOfNames and put the names in A1:A10, and repointed the dropdown to these names.

In B1:B10 I put =IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),0,1) to see if the names were taken.

When the macro kicks off, it checks to see if the names are still available. If it's not, it gives a pop up. Otherwise it goes on as normal.

[LEEDA12345]

Thanks for all your help I have added some reputation saying your are very helpful I just have one me more question if I add a new record to the table will the formula code pick it up I promise it's the last question hahaa

[daffodil11]

I'm very sorry, but it will not.

If nothing else, tweeking the code will give you some familiarity with VB.

[LEEDA12345]

Ok maybe if I make the range of the table A1-A300 for example and when I add the new record it will pick it up?

[LEEDA12345]

So up to 100 is text rest is blank when I add the new recorded it will pick up 101 now had text

[daffodil11]

Indeed. That should work fine.

You'll have to add rows to the VLOOKUP range as well and then copy column's A's list to ListOfNames so they appear in the dropdown. You can edit the dropdown by going to Data - Validation and expanding the list there.

[daffodil11]

If Application.WorksheetFunction.VLookup(x, Worksheets("ListOfNames").Range("A1:B300"), 2, 0) = 1 Then

MsgBox ("This name chosen")

Else: For Each c In Sheets("Sheet2").Range("A1:A300")

[LEEDA12345]

How would you do that just expand the lookup range?

[LEEDA12345]

Sorry I just seen your post on how to create it thanks

[LEEDA12345]

Where do it write this do I just add it to the macro code?

[daffodil11]

I was just highlighting the parts of the code you need to change. It's already there, you just need to change things that end in 10 to 300 instead.

Then you'll need to go select your dropdown list, go to Data tab -> Data Validation and change the list reference from A1:A10 to A1:A300.

Lastly, you'll need to copy Column A from your table onto the ListOfNames tab, and continue the formula from B10 down to B300.


Feel free to leave yourself notes between lines of VB code. If you start the line with an apostrophe, anything after that is just considered a comment.

for example:

Please Login or Register  to view this content.

You'll know you're making comments correctly when you hit enter and the line turns green

[LEEDA12345]

Thanks I will do it tomorrow I'll let you know how it goes once again thanks

[daffodil11]

Okie dokie

[LEEDA12345]

How do I mark this as solved?