Excel/Paste Value VBA if criteria met
I hope you can Help me.
I have a 3 worksheets, each sheet starts from row 5. All 3 sheets are identical fields only data is different. Each sheet has 5 cells above the data rows: Y2, Y3, AF2, AG2, AH2. I rarely, but may have to update these 5 cells' values after anywhere between weeks to months. Otherwise, I rarely revise them.
Each sheet has column A that contains many names. Column Y holds the values – either “AUTO” or “MANUAL”. The ranges between A to Y will be populated manually by me daily. Every day I open the workbook, I will fill up data in a few new rows from A to Y.
What I need:
1)paste value the value of Y2 into column Z cells of new rows I have today entered that satisfies the criteria that the new cells in column A contains "SAL" or "BOB" and the new cells value in Y is “AUTO”,
paste value the value of Y3 into each new day’s new populated rows column Z cells that satisfies the criteria that the cell in column A contains "SAL" or "BOB" and the value in Y is “MANUAL”;
2)paste value the value of AK2 into column AA cells of new rows I have entered that just need satisfy the criteria that the new rows’ cells in column A contains "SAL" or "BOB";
3)paste value the existing value of AJ2 into column AB cells, AL2 into column AC cells of new rows I have today entered that satisfies the criteria that the new rows’ cells in column A contains "DAN".
These names "SAL" or "BOB" or "DAN" reappear in each 3 sheets, but in different rows.
The reason that I need paste value is because I need ALL the rows with previously pasted different values (in previous upper rows) since yesterday into the past in columns Z,AA,AB,AC - to remain pasted there forever so that I can read them as historic data in future time for reference. This is why I cannot write a function formula within every cell in Z,AA,AB,AC which would cause inevitable recalculation of every cells in Z,AA,AB,AC to the latest values each time this workbook is opened - which is not what I want since old rates of the 5 cells would be lost forever.
I just need the VBA to run each time only at CLOSE of the workbook.
It's a simple task, but I'm not trained in VBA.
I wonder if you who is well-versed in VBA can help?
Thanks for the detailed question. Let me rephrase the question as per my understanding and try to answer it:
You are looking for a value in column Z based on certain logic but you don't want to write a formula because you don't want recalculation.
There are 2 options:
1. You can just record a macro to copy and paste values only in column Z and run it on a daily basis.
2. I can create a macro to paste the result of your logic in column Z on a daily basis.
If you'd like me to create and give a macro, please send me your excel file with detailed explanation of your requirement and sample results and I can write the macro and send it back to you.
Please send your file to firstname.lastname@example.org and do include AllExperts in subject line.