How do I get my userform VBA to copy and paste a specific row from a worksheet into another worksheet based on multiple complex conditions?

[benjaminingreens]

I have rudimentary VBA experience, but I have a specific problem which I need VBA to solve.

Here is my best attempt at explaining what I need VBA to do (with some context):

Context on How the Spreadsheet Works I have a worksheet called 'Block Chain' which logs a large amount of data for a project every time a change is made to information associated with this project. For example, 'worksheet 1' is a place in which someone could change the 'project manager' associated with 'project a' amongst other things. The changes are made with userforms on various worksheets.

Every time any change is made to project information, all information associated with that project (including the new change) is stored on the 'Block Chain' worksheet in the next available row. This row will contain information like, 'project name', 'project sponsor', 'budget', and 'project manager' etc.

So let's say I change the 'project manager' from 'Steve' to 'Ben' for 'project a' on 'worksheet 1'. Once I have done this, the next available row in the 'Block Chain' worksheet (row 100, for example) will store all current information for 'project a'. The cell in the 'project manager' column on this row will say 'Ben', because this is now the project manager for project a.

Let's say, after a few months, I change the 'project manager' again from 'Ben' to 'John'. After this, the next available row in the 'Block Chain' worksheet (row 250, for example, since a few months have passed) will store all current information for 'project a'. The cell in the 'project manager' column on this row will now say 'John', because this is now the project manager for project a.

What I Need VBA to Do This is the part I actually need help with. All the above already happens when a userform in the spreadsheet is submitted through VBA. I need to add some additional code which does something quite specific every time a userform in the spreadsheet is submitted.

Firstly, I want this code to scan the 'Block Chain' worksheet and find the last row of data for any given project and store this in a dynamic variable -- say, 'last-row-for-project'. In addition, I want this code to scan the 'Block Chain' worksheet and find the penultimate (second to last) row for any given project and store this in a dynamic variable -- say, 'penultimate-row-for-project'.

By way of example, for 'project a', 'last-row-for-project' is row 250, and 'penultimate-row-for-project' is row 100.

I then want the code to apply some conditions to these dynamic variables and perform some tasks depending on the outcome of these conditions.

Here are the conditions:

1. If 'cell in any given columns(x)' in 'last-row-for-project' is not equal to 'cell in the given columns(x)' in 'penultimate-row-for-project'
2. And 'cell in specified columns' in 'last-row-for-project' is equal to 'specified value: NEW'

And here is what I want the code to do if the conditions are TRUE:

1. Copy 'last-row-for-project'
2. Paste 'last-row-for-project' into 'worksheets(z)'

Note: 'Worksheets(z)' will need to be worksheets that have equivalent names to 'columns(x)' in 'cell in any given columns(x)' (as specified in condition 1 above). So 'worksheets(z)' is also a dynamic variable that could represent multiple worksheets, and therefore could result in the "Paste 'last-row-for-project'" action being applied to multiple worksheets.

So, this code will compare cells in ranges "columns(x):'last-row-for-project'" and "columns(x):'penultimate-row-for-project'" in the 'Block Chain' worksheet. If these cells are different, and if "column 1:'last-row-for-project' = NEW", then the code will copy 'last-row-for-project' and paste it in all the worksheets that have the same names as the columns in which there were different cells (between 'last-row-for-project' and 'penultimate-row-for-project').

To refer back to my initial example, the code would copy row 250 (provided condition 2 is also TRUE), and paste it into the 'project manager' worksheet, because this is the name of the column in which the change was made for 'project a'. If I had also changed the 'project sponsor' for 'project a', I would want the code to also paste row 250 into the 'project sponsor' worksheet as well as the 'project manager' worksheet. This is why I say 'worksheets(z)' needs to be a dynamic variable that can store multiple spreadsheet within it.

I really do need help with this quite desperately, so please please let me know even if you know how some of this code would work. Thank you very much.

 

[sijpie]

You mention you want the code to run when the user presses the OK button on the userform, directly after adding the changes to the BlockChain sheet.
So the code does not need to scan for the last change, as it is the last row. Correct?
So you then only want to find the previous entry (penultimate). Correct?

There are a couple of fast ways to find the penultimate row.
a. Load the blockchain sheet into an array, and do the searching in the array. You can then also do your comparison in the array.
b. use the Range.Find() method, by setting the range to the column with the project name, and doing the find in reverse order starting from the last cell (the row you just added)
then you can do your comparison on the row you found.

Look up the Range.Find method to see how it works.