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]

Yes, we can do that but how do you want do this?
Do you want to nominate a start date from which to clear down, I can provide an InputBox in which you'd supply the date, or do you always want to keep everything from "yesterday" and before? If that is the latter I can clear from "today" into the future before moving Today's requests, and you'd never have to supply the start date. I'm assuming therefore that "yesterday" you dealt with the future requests before overwriting.

 

[Yulyo]

In Sheet2!A16 i will always have the a date, from which i must delete the row in Sheet1.
So, if in Sheet2!A16 i have the date "15.10.2017", i must go to sheet1, column D select and delete the cells starting from 15.10.2017.



Or i can click on a cell on Sheet1 and after that run the macro. Macro should delete the column starting from selected cell, and after this import the numbers from Sheet2.

 

[BrianJN1]

I've worked from from Row 2 in my macro.
I've taken Sheet2!A2 as my Start date from which to clear contents in Col D of Sheet1.

I've annotated those changes within your macro. Here then is the code which I think now meets your requirements:

Sub ReplaceData()
Dim lastRw1, lastRw2, nxtRw, m
Dim StartRow, x
Dim StartDate As String
Dim FndRw As String

'Determine last row with data, Sheet1
lastRw1 = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Get start date and its row on Sheet1
StartDate = Range("Sheet2!A2").Value
For StartRow = 1 To lastRw1
If Range("Sheet1!A" & StartRow).Value = StartDate Then
FndRw = StartRow
Exit For
End If
Next
'Clear Contents from Startdate down
Range("Sheet1!D" & FndRw & ":D" & lastRw1).ClearContents
'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

 

[Yulyo]

It's perfect now
Thank you very much Brian! You made my day!
Best regards!

 

[BrianJN1]

And Thank you, you've help finish my day in that I accomplished something.

 

[Yulyo]

Of course my headache it's not finished yet. In sheet2 on my main file, i have some other dates in columns E:L and the macro copies the entire row. Any ideea what to change so only the cells in column d will be copied?

 

[Yulyo]

Done!
Sheets(2).Range("A" & nxtRw & ":B" & nxtRw).Copy _

 

[BrianJN1]

Self help? Well done.