Import data from sheet2, after clear column data in sheet1

[Yulyo]

Hello all,

Please help me with a formula in sheet1 D17, to do this: (please download the test file from my Drive account)

https://drive.google.com/open?id=0B9tH3lTneqQUdWkySHlqVGFNQTg


- search for 16.10.17 in sheet2, column A, and copy the corespondent number from sheet2 column D, to sheet1 column D (in cell D17)

- sheet2 will be updated daily, so maybe tomorrow i i will not have a request in 16.10.17. In this case, the request in sheet1 cell D17 must be empty too.


I just found a VBA, but it does not solve the second problem, meaning does not delete the old values.
So if today I have a request for 15.10.2017, and after the tomorrow's update I have an request in 14.10.2017 and 16.10.2017, this vba does not delete the request from 15.10.2017. The thing is that i only need to delete the data starting from smallest date in sheet2, column A... (in this case, to clear cells in sheet1, starting 16.10.2017)

I hope i made myself understood.
Thank you in advance for any help you cand give. I'm really stuck right now...



This is the code i just found:
=======================
Option Explicit
Sub ReplaceData()
Dim lastRw1, lastRw2, nxtRw, m
'Determine last row with data, Sheet1
lastRw1 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Determine last row with data, Sheet2
lastRw2 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
'Loop through Sheet 2, Column A
For nxtRw = 2 To lastRw2
'Search Sheet1 Column A for value from Sheet 2
With Sheets(1).Range("A2:A" & lastRw1)
Set m = .Find(Sheets(2).Range("A" & nxtRw), lookat:=xlWhole)
'Copy Sheet2 row if match is found
If Not m Is Nothing Then
Sheets(2).Range("A" & nxtRw).EntireRow.Copy _
Sheets(1).Range("A" & m.Row)
End If
End With
Next
End Sub
=======================

 

[BrianJN1]

Let me clarify. At the time that Sheet2!D17 is copied to Sheet1!D17, Sheet2!A17 is to become void, or is D17 to be voided?
I am trying to understand exactly what you mean here:

So if today I have a request for 15.10.2017, and after the tomorrow's update I have an request in 14.10.2017 and 16.10.2017, this vba does not delete the request from 15.10.2017. The thing is that i only need to delete the data starting from smallest date in sheet2, column A... (in this case, to clear cells in sheet1, starting 16.10.2017)

Am I believing that the request moved to Sheet1!D17 is not to be deleted? But That at Sheet2!D17will go? We are only concentrating on "today's" date, right?

 

[Yulyo]

Let me clarify. At the time that Sheet2!D17 is copied to Sheet1!D17, Sheet2!A17 is to become void, or is D17 to be voided

Sheet2!A17 will remain the same. I am not changing any data to Sheet2.

I changed the file a little bit, please download again from this link:
https://drive.google.com/open?id=0B9tH3lTneqQUeEdfVV9mSmRlYjA

Thing is like this:
- if today i recieve a request from customer (sheet "first_request"), that i must send 444pcs in 16.10.2017 (first_request!D2), i copy the 444 (pcs) from first_request!D2 to delievery!D17.
- if later today i will recieve another request from customer (sheet "second_request), that i must send 555 pcs in 17.10.2017 (second_request!D2) and 666 pcs in 23.10.2017 (second_request!D3), than i must copy 555(pcs) from second_request!D2 to delievery!D18 and 666 (pcs) from second_request!D3 to delievery!D24. after this i must delete the value in delievery!D17, because the request changed and i don't have any request in 16.10.2017.

Thank you!

 

[Yulyo]

So right now i must edit the above vba, so when i execute it, to first delete the numbers in sheet named "delievery" column D, stating with the exact same date as the first date in sheet "first_request" column A.
Any help?

 

[Yulyo]

Sheet 1 initially:

Date	Request
16.10.2017
17.10.2017
18.10.2017
19.10.2017
20.10.2017
21.10.2017
22.10.2017
23.10.2017
24.10.2017

<tbody>
</tbody>

Sheet 2 initially:

Date	Request
16.10.2017	444
17.10.2017	555
23.10.2017	666
24.10.2017	777

<tbody>
</tbody>

After the first update, Sheet1 should look something like this:

Date
16.10.2017	444
17.10.2017	555
18.10.2017
19.10.2017
20.10.2017
21.10.2017
22.10.2017
23.10.2017	666
24.10.2017	777

<tbody>
</tbody>

Tomorrow i will recieve a new request (Sheet2), that looks like this:

Date	Request
17.10.2017	888
24.10.2017	999
26.10.2017	1111
28.10.2017	7878

<tbody>
</tbody>

After the second update, Sheet1 should look like this:

Date	Request
16.10.2017
17.10.2017	888
18.10.2017
19.10.2017
20.10.2017
21.10.2017
22.10.2017
23.10.2017
24.10.2017	999
25.10.2017
26.10.2017	1111
27.10.2017
28.10.2017	7878
29.10.2017

<tbody>
</tbody>

 

[BrianJN1]

I shall check this in my morning.

 

[Yulyo]

No hurry Brian,
I have attached 2 print screens with what exactly i need. Maybe you will understand better.
Thank you for taking your time to help me.


https://drive.google.com/open?id=0B9tH3lTneqQUckVmenhZZUdhX0U

 

[BrianJN1]

Try this:

Sub ReplaceData()
Dim lastRw1, lastRw2, nxtRw, m
'Determine last row with data, Sheet1
lastRw1 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
[B]Range("Sheet1!B2:B" & lastRw1).ClearContents[/B]
'Determine last row with data, Sheet2
lastRw2 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
'Loop through Sheet 2, Column A
For nxtRw = 2 To lastRw2
'Search Sheet1 Column A for value from Sheet 2
With Sheets(1).Range("A2:A" & lastRw1)
Set m = .Find(Sheets(2).Range("A" & nxtRw), lookat:=xlWhole)
'Copy Sheet2 row if match is found
If Not m Is Nothing Then
Sheets(2).Range("A" & nxtRw).EntireRow.Copy _
Sheets(1).Range("A" & m.Row)
End If
End With
Next
End Sub

My bold line clears data in Sheet1 Column B (other than the header) before your Macro continues as I suspect that you expect.

 

[Yulyo]

Hello BrianJN1,
Thank you for your help It does 90% of what i need. I just don't need to clear the entire column.
Is there a chance to specify this: clearcontents starting with selected cell?

10x

 

[Yulyo]

Or maybe, clearcontents on Sheet1 column B , starting with a date from Sheet2 column A?