using VBA moving a row in a table to another table. vice-versa

[SpencerDelande]

Using VBA

I have 3 tables.

Each table has 3 columns.

When I click inside the table.
Let’s say Cell C19
It shows the info from the row it is in
To the box that says "Equipment want to move".
And it shows what current table it is in, in the box that says "Current location".

Then the next part is to click on a table header.
So I click on Table 2 which shows Table 2 in the "Location move to" box.

Then I click on the move button and the data moves from the old location to the new location and sorts the table alphabetically (column 2 of the table).

I Made a before and after screen to help and highlighted an example.

The data can move from Table to Table.

Eventually more tables will be added to move data across but this is just an example of what I am trying to do.

Thank you for your time!

[Kaper]

I think that just clicking to select data is not a good idea. May be right-clicking would be better suited?
So in standard module there are two procedures (I added one and a button for it, to clear the "review_table").

Please Login or Register  to view this content.

and in the sheet module (right click on tab and select View code) the handler for right click

Please Login or Register  to view this content.

I also removed your manual formatting to tables and used table styles (it will keep the borders in good order).

[SpencerDelande]

Thank you so much that helps a lot!

[Kaper]

Glad to hear so,

Once you assure the solution works, it would be a good idea to mark thread as Solved.
Above your first post you will find Thread Tools to do it. It would be lovely if you could also "add me some reputation" by clicking a small star icon in lowerleft corner of my post and then confirm in pop-up message.

[SpencerDelande]

I have an additional question. Once I started adding more tables the code still worked, but if I add tables underneath Table1 Table 2 Table 3, I will eventually run into an error.

If I move the data from Table1 to Table2 enough times I will run into table5 with a debug error.

Using VBA how would I prevent this from occurring? I would always like to have one blank row after the table, separating it from the table below. I will eventually have tables

below Table4 Table5 Table6 as well.


I have attached an excel sheet for a visual.

Thank you for your time.

[Kaper]

Well, original sample file had only one row of tables. I made a code working with potentially more than 3 in a row but not next row of tables.

Tray replacing move_btn macro with a new one - just one line of code was added.

And I think that it would be better to keep two empty row distance between tables . and impotrant think: keep them aligned vertically so if you add another row - do it stright below the current ones - in column B:D, F:H and so on.

Please Login or Register  to view this content.

If you move last element from one of tables, the table will keep one empty row (not just headers). This is a bit special case and is handled with "if - then" construction. Otherwise the vertical distance between tables would grow while moving some elements to this empty table.

[SpencerDelande]

The previous VBA worked great.

I have another problem.

The original Sheet we worked on is now called LocationSheet.

I added another sheet called MasterSheet which is a table that has all the data but added a column called location.

In which, when a piece of equipment is moved from LocationSheet from one Table to another Table I want it to automatically update the location column on the Masterlist on the MasterSheet.


I added another Sheet called CatagorySheet which shows the various Equipment (A,B,C,D,E Tables) and what locations the Equipment are at.


Example: On LocationSheet I move Equip # 5 B from Table 1 to Table 2 using the Move Button, on MasterSheet Equip # 5 B location will change from Table 1 to Table 2.

Then on CatagorySheet, B Table, Equip # 5's location will change to Table 2. I highlighted the example in yellow.



Thank you for your time.

[Kaper]

Try:

Please Login or Register  to view this content.

BTW. Standard approach would be to have MasterSheet as only place to make changes, and have other two sheets being reports generated on the basis of this MAsterSheet.

Changes there (in Mastersheet) could be as easy as seletcting a new location from the dropdown list in a given cell. If needed, Category could be also selected that way.

[SpencerDelande]

Thank you!! I understand Mastersheet is the standard to make changes but using LocationSheet to make changes is way easier for this scenario.