Further to your above reply:-
Yes, you're correct, there are notes and info from line 40 down so the data transfer will be between a specific range of rows as you said, (A7:A40) from sheet1 to transfer to sheet2 from row 26 and below,
The data does transfer from row 26 down, but I'm trying to get my head around the code, it's not so easy to decipher.
That's OK. Seeing that you are transferring each row individually as a task is completed, the range gives you plenty of room to move but I still recommend that you clear the data from sheet 1 once it is transferred because if you don't, you will run out of room based on the fact that your dataset is restricted to A7:A40. Clearing the "used" data leaves a clean sheet for continuous input, especially in a restricted range and, what's more, you will still have a record of the same data in sheet 2 (and I am assuming that you back up on a daily basis).
Not clearing the data from sheet 1 will result in continuous duplicates in sheet 2.
Sheet 2 is obviously a "clean sheet', so you can replace this line of code:-
Sheets("Sheet2").Range("B100").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
To explain this line, the code searches sheet 2 from the bottom up looking for the next available row to place the latest transferred data and, with a "clean sheet", this happens quickly and uninterrupted. The PasteSpecial xlPasteValues bit is a method of transferring the cell values only (no formatting, formulae etc.).
The idea is to create a sheet that automatically populates with the data from sheet1 to sheet2 to eliminate the copy and pasting of data every time a report is filled as only the data that has a value in it from sheet1 column B is required as its a completed task.
(I hope I'm making sense)
This is what the code does: transfers a row of data based on a value being placed in Column B. No buttons required, no manual running of the code. As mentioned earlier, it is a Worksheet_Change event (which is why the code is placed "in the sheet" rather than a standard module) which means, basically, something will happen on the active sheet every time something else happens (an event). In this case, that event is entering a value in a cell in a row in Column B then moving to the next cell by clicking away or using the arrow keys on your keyboard thus triggering the transfer of data. Basically, Column B is an "active" column waiting for your input to trigger the transfer of data, so, as you fill in data row by row, it is best to leave the Column B input 'til last.
If you're interested, there is another way of having Column B as an active column, transferring data by individual rows at a time, but it will involve a button.
I'd like the data to remain in sheet1.
Not a good idea with a restricted range. Refer to my first answer above.
Also does the data transfer complete box have to pop up every time data is entered into column B? That has to be ok'd before I can continue each time.
Also, if I make a mistake with data entry, ie, I enter 5 into B7, then realise it should be 0, the data doesn't reset on sheet 2 when a 0 value is re entered,
The message box is not necessary. To remove the message box, delete this line of code from the macro:-
MsgBox "Data transfer completed!", vbExclamation
As with anything like this, you will need to check that your inputs are correct prior to transferring. If you have transferred data with an incorrect entry, the corrected entry will not overwrite the previous in sheet 2. You will have an additional row of data in sheet 2 so you will need to delete the incorrect entry from sheet 2.
Or, are you saying that a row of data with an actual zero (0) value in Column B is not being transferred to sheet 2?
I hope that all this helps.