You are here:

Excel/Paste Value VBA if criteria met


Hi Gulshan,
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?



Dear Jason,

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 and do include AllExperts in subject line.


About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts




I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.


I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2017 All rights reserved.

[an error occurred while processing this directive]