copy+paste values if value does not exist

Dear all,

I know that these days I should focus on something else, but I just cannot take it out of my mind and cannot find another way to do it but with macros. So I need some help with the following:

1. When Excel file opens up the macro copies from H11 to H16 and paste special with truncate in sheet2 in the next available empty row from A2, if date (Sheet1!H11) does not exist in Sheet2!column A:A.

2. Right after that, the macro copies the values from G11 to G16 in sheet1 and pastes the values from H11 to H16 (sheet1).


3. G11 date is equal to Filedatetime: C:\Users\admin\Desktop\book1.xls

The macro in point 2 two it's easy and I have even managed to make it when Excel opens up. Point 1 is too complicated for me because of the if condition. Point 3 I have tried many times adding the path but always get sseveral and different errors. Don't know exactly what I am doing wrong in there.

Many thanks all for your help. Attached the example.

The point of this thread is because the values from G11 to G16 will change on a weekly basis, as soon as the original data source is updated(G11 to G16 contain Index/Match formulae to book1.xls ). That's why I need to keep historical records of them in sheet2. Formulas will only capture actual values.

I hope I have explained myself clearly.

Many thanks Davesxcel!

Hi Jaslake,

First of all many thanks for your reply and for your support.

Clarifications:

Point 1: Yes, you are totally right. I meant transpose and not truncate. Many thanks!

Point 3: I need to extract the date (last modified/last saved date) of a external workbook or Excel file and this date to be reflected in cell G11. File location: C:\Users\admin\Desktop\Book1.xlsx.

Feedback:

1.When I open the workbook nothing happens, unless I run the code from the Visual Basic module. I also tried to save it with the declaration Workbook open, but never worked. I would like the action to happen when opening the workbook.

2. The code cuts the values from H11 to H16 instead of copying, is that something we can change? I need to see those values. I assume that you've gone for cutting the values because the next action would copy values (H11 = format cell = date) from G11:G16 to H11:H16, correct? The thing is that I cannot see that part of the code. I have recorded a macro to do that but every time it runs it copies the value as a number, even when I saved that cell with date format. Can you help with that?

Many thanks, really!!!!!!

Sorry for the long delay Jaslake!

I managed with your code apart from a few minor modifications and I also added by myself to implement FileDateTime. So, I am going to mark this thread as resolved.

Many thanks!