VBA for cutting and pasting a row based on one cell

Hi All,

Very new to this but i was hoping some one could help. I have a document that has a number rows of information in a number of sheets. I would like to be able to enter either "Coach" or "Referee" into Cell AF on the sheet (Online Registration Report) and have the entire row move to a separate sheet in the workbook either (Previous Referee Data) if referee was entered, or (Previous Coaching Data) if Coach was entered

Or alternatively if possible (below preferred)

Be able to put an "x" in Cell AF and have the row cut and pasted into the relevant sheet based on a value in cell P (Coach or Referee)

I also need the paste to be in the next available row on the new sheet as the other two sheets are already heavily populated and it cannot overwrite the previous data.

Finally if it can be done i would like to remove the text from cell AF and have the row return to the original sheet (Online Registration Report)

If there is a VBA code out there for this i would be very grateful if some one could assist.

Hi,

When you say that you would like the data to return to the original sheet, do you mean in the same spot that it came from, or is the bottom of the list alright?

Thanks

There are a few things to clarify.

So, to summarise, you have 3 worksheets in the same workbook?
Sheet 1: Online Registration Report
Sheet 2: Previous Referee Data
Sheet 3: Previous Coaching Data

You know cell addresses are similar to X and Y coordinates right? You want to be able to put "x" in cell AF? on which row? first row? if it's the first row, do you have more than 1 row of data? or do you mean column AF? so whichever rows that contain an "x", will be cut and pasted to the respective worksheet based on column P of the same row?

You want to cut and paste into the destination worksheet, and you want to have the row number (of which it was pasted to) returned to where "x" was. So did you mean copy and paste instead? otherwise you would have a blank row of data, and only the row number stored in column "AF"

@JM88: Here is a mocked-up spreadsheet which does what I understood your request to mean.

I have commented the code so that you can copy and paste it into your own workbook and make the necessary changes with relative ease.

Hope this helps

@Ajryan88- Brilliant this is working very well.. Just two things,
1stly if i delete the "x" out of the Column AF the row wont return back to the original spreadsheet. I don't mind where it goes back in the original spreadsheet. Can this be fixed? I am also unable to undo the move, it simply erases the info and saves it?
2ndly - The run-time error '13' - Type mismatch keeps coming up every time i do somthing to the data?

Are these fixable? Thank you very much for all this so far!

Oh and no it can't be undone. Changes made with VBA can't be undone like a worksheet change

Hi All,

Happy New year. I had some brilliant assistance from this forum with a problem i had mid last year around VBA coding and a database that i have developed using Excel.

The coding seems to have changed an no longer works. Is any one able to help me please? I am still a novice when it comes to VBA coding. I have attached a sample of the database here for everyone to see.

Simply i would like the coding to allow me to move a row from one sheet to another based on a value in a column (in this case based on their qualification "coach", "referee", "Presenter" )

In addition to this i have a code that allows me to click a button and will then save the sheet as a separate workbook which allows me to send it with out sending the entire document. However i would like this button to lock the sheet at the same time when it saves. Is this possible?

Any help would be greatly appreciated.

Kind regards

JM88

Hi,

Did you copy the worksheet code for ALL of the 3 worksheets? Each of them has its own code.

If you have and it still isn't working, can you please upload your workbook for me to have a look at?

Thanks