Forum Discussion
Urgent Help Needed copy and pasting Values only when a condition is met
8 Replies
Basically, to replace a formula by a constant value requires human action or a macro.
There are tricks that give the result you require but they could cause problems using the workbook. If you allow circular references with a single step then
= IF( (date=today)*(thisCell=0), value, thisCell)
will copy the value into 'thisCell' provided it is initially blank and the dates match. Otherwise it will reference itself and retain its existing value. If you try it, I would be interested to know how you get on! Maybe I am being unduly pessimistic.
Hi Peter,
Unfortunately, this does not work. I would be very grateful if you could review my response to Nikolito as I have tried to be as clear as possible in my response.
Thank you in advance
Ian
There are some undesirable characteristics but it should work.
Firstly the final table should not use text for the date series (not an Excel Table).
The workbook must allow circular referencing.
To clear values once set the formula has to be cleared and re-entered by copying across from an adjacent cell using Ctrl/Enter.
If you set out to use VBA try to limit the transfer to reading and writing the cell values rather than copy/pasting all the formatting properties along with the value.
From the picture I can not recognize or understand your plan (maybe also the translation).
Since the possibilities in Excel are very diverse, the possible solutions are also diverse.
Therefore, with your permission, please download a file (without sensitive data) with your request.
At the same time, I would ask you to name the Excel version and the operating system, as there may be different approaches to the proposed solution.
Thank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
Hi Nikolino,
Thank you for your response, I will try to be clearer.
In the first screenshot, this is my source data table. This data flow has a connection to a SharePoint list so it is constantly changing (I have greatly simplified the data set).
The second screenshot is a summary pivot table that shows me how many projects are "In Progress" or "Not In Progress". This table is also continuously changing as the source data changes.
The final screenshot is the table that I am trying to populate. What I want to do is copy the data from the summary pivot table (2nd screenshot) every Friday (When the date condition is met) but I do not want that data entry to change as the source data changes. This will allow me to populate that table over time and see the trend on a week on week basis.
Please let me know if you can help as I am really stuck.
Thanks in advance
Ian
As far as I could understand (... thanks to the translation, or maybe not) you want to copy certain areas from a workbook / worksheet to a new workbook / worksheet.
If this is the case, then maybe this VBA code could help you, you just have to set it up according to your ideas and let it run with a button.
It's just an idea.Sub TestCopypaste () Range ("A2: L2"). Select Selection.Copy Windows ("target mappe.xls"). Activate ActiveSheet.Paste Windows ("Data mappe.xls"). Activate Range ("A5: L5"). Select Application.CutCopyMode = False Selection.Copy Windows ("target mappe.xls"). Activate Sheets ("Table2"). Select ActiveSheet.Paste Windows ("Data mappe.xls"). Activate Range ("A8: L8"). Select Application.CutCopyMode = False Selection.Copy Windows ("target mappe.xls"). Activate Sheets ("Table3"). Select Range ("A2"). Select ActiveSheet.Paste End SubThank you for your understanding and patience
Nikolino
I know I don't know anything (Socrates)
Hi thanks for your response.
This doesn't do what I need because it will always be referencing that cell. So as the date changes it will appear blank. Once the date criteria is fulfilled I want it to paste the value so that it is then static and will not change so I can see the trends on a week on week basis. Do you know how I can do this? Is there a paste values formula? I can't seem to find one.
Thanks
Ian
