Automatically entering a row in another tab, triggered by a specific value in a cell

All,

I have a spreadsheet with columns A-Y with various data. One a second tab, I want to copy an entire row (A-Y) to the next available empty row. The action needs to be triggered by Column Y when from a list, "100" is selected.

Thanks in advance for your help. This level of code is WAY over my head. I do traditional IF/Then statements, VLookup, math formulas so please provide any explanations with that in mind.

Patrick L

Hi Patrick
Welcome to the forum.
If you want an "automatic" VBA solution the attached 'bare bones' macro should get you going.

Originally Posted by barryleajo

Hi Patrick
Welcome to the forum.
If you want an "automatic" VBA solution the attached 'bare bones' macro should get you going.

Thank you very much. As a novice, I hope I can ask you one more bit of advice. I opened my spreadsheet and copied/pasted all of your code, including the heading, to the macro page. I changed the sheet names to match but I can't get it to run when I enter "100" into cell "Y". Nothing happens so I know I did something wrong. I'm not getting any error messages. Please advise and thanks again.

Patrick L

Hi Patrick
I'm sorry this didn't work for you
I have re-attached the spreadsheet - there is nothing to do!

On Sheet1 simply type "100" in col Y (blue shaded cell) and press Enter.
Then select Sheet2 to see that the row has been copied to the bottom of the list.

The macro makes use of an Excel Event - a Worksheet_Change event.
This is where the code is located.
The event is triggered automatically each time a cell's content is changed.
The event passes the changed cell address to the macro as "Target" to be processed.

It likely didn't work because you copied the code from the Worksheet_Change location in the VBA editor.

Hope this works for you now.

Originally Posted by barryleajo

Hi Patrick
I'm sorry this didn't work for you
I have re-attached the spreadsheet - there is nothing to do!

On Sheet1 simply type "100" in col Y (blue shaded cell) and press Enter.
Then select Sheet2 to see that the row has been copied to the bottom of the list.

The macro makes use of an Excel Event - a Worksheet_Change event.
This is where the code is located.
The event is triggered automatically each time a cell's content is changed.
The event passes the changed cell address to the macro as "Target" to be processed.

It likely didn't work because you copied the code from the Worksheet_Change location in the VBA editor.

Hope this works for you now.

I took your spreadsheet and copied my headings and data into it and it worked. Then I added the master spreadsheet where all the data is entered. I changed the code to Sheet3 and it still worked. Then I linked all of the data EXCEPT the "100 Y" section from the master spreadsheet to my second sheet. That second sheet drives the third sheet copy/paste function. Basically, the second sheet pulls only some of the columns from the master. The third sheet will capture only the top 100 accounts from what will be hundreds from the second page. The problem is when I linked all of the cells to the master, the function stop working. Each cell has a simple If/then statement like: =IF('DMC General Data'!Z12="","",'DMC General Data'!Z12). It just keeps unused cell clean without a zero. I am so sorry to be taking your valuable time, but making this function work will save us a lot of time. I really appreciate your help and especially your patience.
If this function won't work from linked cells, I can pull the data from the master spreadsheet but the code would have to allow for skipped columns. Example A:C,E:G,J:T etc. Thanks again and again. Patrick L.

OK Patrick
Glad you are making some headway.
Perhaps you could upload a copy of your latest spreadsheet and put in an explanation of exactly your requirement.
If I can help then I will.
Barry