run vba at close worksheet to paste values to a range


New Member
Jul 10, 2016

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 AG2 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 AF2 into column AB cells, AH2 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 Y, 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.

By the way, 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 anyone who is well-versed in VBA can help?



Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...