Struggling with Cut and Paste macro between sheets

[liammcbeth]

Hi Guys and Girls

Having a bit of problem with trying to work out a macro to cut and paste between sheets.

I have 4 sheets within the workbook one is all active suppliers and 3 conditional ones depending on whether the supplier is black listed etc.

I'm trying to have some macro buttons that will allow me to select a row of data and then cut and paste to another sheet. The idea is to have one button for each other sheet so I will have 3 buttons all with the same macro but mapped to a different sheet.

I then want to add another macro on each one of these sheets that will do the exact same thing but send it back to the active suppliers sheet. I want the data being copied between sheets to go into the next blank row so that the macro doesn't override/delete/lose data that's all ready there and deletes the now empty row from its original location before running the macro.

If anyone can point me in the right direction it would be much appreciated. If I can get it to work once on the spread sheet then I can just copy the code and modify for the other sheets etc.

Many Thanks

[liammcbeth]

And here is the spreadsheet (If it works)

[vcoolio]

Hello Liam,

I've just had a rummage around your work book and the codes you have generally do the required task. What problems are you having?
Some codes are in the work sheet modules whereas they should be in standard modules.

I would say that one code would be enough to transfer data between sheets (any sheet to any sheet).

Can you enlighten us further?

Cheerio,
vcoolio

[liammcbeth]

Hi vcoolio

The spreadsheet is kind of a stop gap for the next 6-12 months whilst a new IT system is developed.

When I populate it with data there will be around 8000 active suppliers with about 1400 in the other tabs. The spreadsheet will be used by several different department heads and members of quality with varying degrees of IT abilities, and will be in constant use with data being moved around the various spreadsheets. I'm trying to make it easier/quicker to cut a row out and drop it into another spreadsheet and deleting the row by using a macro (Reduce the risk of people accidently deleting the row above or below etc) and reduce the amount of manual data manipulation. I would like to keep in place multiple buttons for the various sheets so I can colour code the Macro button to the appropriate tab and attempt to make it "idiot proof" from a user perspective.

The issue is I'm not sure how to get it to cut the data and dump onto another sheet and delete the row for any row rather than just the top line etc.

[vcoolio]

Hello Liam,

I've just knocked up a test work book at the following link (this site's file uploader still doesn't work for me):-

https://www.dropbox.com/s/0ym0ad8si0...%202.xlsm?dl=0

It uses one button per page. Select a value in Column A (of any sheet) then click on the button. An Input Box will appear asking which sheet you would like to transfer the data to. Enter the sheet name (in this case: sheet1, sheet2 etc....) and click OK or press Enter on your key board and the data will be transferred to the relevant sheet. The "used" data from the active sheet will be deleted.

Sheets 2 - 4 don't have any data in them to start with so transfer a few rows from sheet1 to each sheet first then play with each sheet thereafter.

This hopefully is a starting point for you. If you would still prefer separate sheet buttons on each sheet, then let me know. I'll get back to you tomorrow.

Cheerio,
vcoolio.

[liammcbeth]

Hi vcoolio

I have downloaded this and its great, already given me an idea for a future piece of work ill be looking at.

Used another member of the team as a guinea pig and we found that it prone to typos when someone is trying to do something quickly. I would still like to look at separate buttons for each sheet, not worried about it not looking too neat just trying to make it easy for users that all they have to do is click.


Cheers

Liam

[vcoolio]

Hello Liam,

I have downloaded this and its great, already given me an idea for a future piece of work ill be looking at.

Good to know that it will come in handy.

Following is the code that I should have placed in post #5. It is a public forum after all and file sharing links can be prone to degeneration:-

Please Login or Register  to view this content.

Now, to do it with individual buttons for each sheet in each sheet, then we'll need four codes as follows:-

Please Login or Register  to view this content.

As you can see, they are identical other than the Sub title and the sheet references. So, in Sheet1 (your input sheet), you'll need to assign the codes Transfer2, Transfer3 and Transfer4 to the buttons that will transfer data to Sheet2, Sheet3 and Sheet4.
In Sheet2, you'll need to assign the codes Transfer1, Transfer3 and Transfer4 to the buttons that will transfer data to Sheet1, Sheet3 and Sheet4.........and so on. I'm sure you get the picture now for each sheet.

Following is the link to my mock-up of your work book showing how it works:-

https://www.dropbox.com/s/nrozpfn0b7...%203.xlsm?dl=0

The buttons in each sheet have been assigned their respective codes. Select a value in any cell in Column A of any sheet then click on any button to see how it works. Again, Sheets 2 - 4 don't have any data in them to start with so transfer a few rows from Sheet1 to each sheet first then play with each sheet thereafter. You'll be able to transfer data from any sheet to any sheet and the "used" data will be deleted from the active sheet.

All the codes are in Module1. You will also see that I have referenced each sheet by its sheet code so you can change these references to suit your work book.

It may be worth creating a copy of your work book, then clear out all codes that you have in it (other than the filter codes as they appear to work OK) and place the above codes in a standard module as per the test work book.

I hope that this helps.

Cheerio,
vcoolio.

[liammcbeth]

This is perfect just what I needed.


Many thanks vcoolio, saved my brain from frazzling.

Liam

[vcoolio]

Hello Liam,

No worries! Glad that I could help. Give us a yell if you become stuck again.

Cheerio,
vcoolio.