Paste from one sheet to another sheet that gets autofiltered for position

[ForgetfulMemoryFoam]

Hello excel-lent people,

I have a workbook that I've gotten some help from this site before that I am once again asking for assistance. In my workbook, I have a database with an ECO number (Order number) that has x amount of part numbers. One ECO can have 15 parts attached, or 50 parts attached, etc..
I have a sheet where I am successfully calling up each ECO from the database with X amount of parts. What I want, is to be able to change any or all of the parts (Which machine has the program finished, etc...) and then click an "Update" button. I have everything "Done" but I am using autofilter on the database to only show the ECO number that was typed in, and I want to click on that first cell and paste. Since I'm not adding extra rows or anything like that, what am I doing wrong here?

I will attach the workbook. The update section is in Module 3 and I have lines around it showing where I'm having the issue. I*Side note, is there a way to change module names?




Thank you for any assistance you can provide!!!

[ForgetfulMemoryFoam]

Here is the workbook. It doesn't look like it attached!

[ForgetfulMemoryFoam]

I keep getting an error when trying to include the module 3 code in here due to sucuri website firewall I'm being told!

[torachan]

I can not open the VBA UserForm - keep getting 'out of memory error'
Suggest you remove what extra code you have added, then upload the base file, then load the new code between code tags so that we can read it and see what is wrong.

[ForgetfulMemoryFoam]

Thank you for checking this out. I have attached a new file and since the userform you suggested in a previous thread works perfectly, I have removed that for the sample. I figured out how to change module names and now you should see a module called ECO update which is on the ECO Search tab as a button to click. I put a number (Thats in the database) in the ECO search, click search, and it populates the cells. When I change the status of a machine program or anything and click Update, pasting is where my problem lies.

Let me know what you think and if this workbook opens properly.

[torachan]

I am having difficulty trying to envisage what you are trying to achieve
You are mixing 'range' referencing and 'table' referencing, if you are going to use 'tables' familiarise yourself with its proper referencing.
Also when setting up blank tables ready to receive data only set with two rows (one header+first data row).
Put in a mock-up before and after sheet to help visualise, in the present there seems far too much 'selecting'

[ForgetfulMemoryFoam]

I understand completely. I have had some time away from excel and now I'm getting back into it trying things I've not done or fully learned yet. I will work on it.

My goal for the sheet I uploaded:

On Search ECO, type in an ECO number and click "Search" button. Then:

-Copy ECO number cell into variable
-switch to "ECO Database" sheet
-Autofilter using the variable to find all part numbers in the ECO
-Be able to select these cells that remain, COPY
-switch to "Search ECO" sheet
-Paste contents here

I have achieved that goal. The reason I want to only copy is if someone closes the sheet out or erases anything in that screen, I don't want it to leave the database.


The next goal:

ECO information is displayed, x amount of parts in the list all with their own comments, machine program statuses, etc...

-Change statuses of any of the parts in an ECO#
-Click "Update" button
-Select all cells in the Search ECO sheet
-COPY data (This is as far as I'm getting)
-Switch to ECO Database
-Using the ECO number that is stored in the variable, autofilter to bring back the data that we had searched for earlier
-We shouldn't be adding any rows, so the amount of cells in ECO should remain the same
-Select first cell in this new range and PASTE.
-Autofilter turns back off
-Sends you back to ECO Search sheet.


I feel the only part I'm missing is after auto filtering the database, I have found the data that I need to paste over, but I can't find a "PASTE" only command.



Maybe I have made this more complicated than it needs to be, but I hope I've explained a little better this morning.
Any feedback is appreciated and as always, thank you for reading!

[torachan]

alternative approach - use a userform to add/edit/update/remove data.
with this method there is no need for sheet manipulation (i.e. autofilter/copy/paste etc.,)
at the outset all data is loaded to an array, this is loaded to the listbox, at this stage everything exists in memory, no need to read/write to sheet until final mods complete.
there is a direct link between the listbox.listindex and the sheet row thus eliminating need for search routines.
both the sheet and the form can be expanded to accommodate more columns of data.
to add a new item first press 'Clear' then enter your data and press 'Add' the rest should be intuitive.