VBA Macro to compare two dates in two sheets, then transfer relevant data to relevant column

JohnH76 · Aug 14, 2016

I have spent the past few days to a week browsing the net, and whilst I have come close to what I am looking for, nothing has quite hit the mark. So now, because I can't actually find what it is I am looking for, and whilst learning a few new things at the same time, I am now posting my question to see if I can find my answer.

Putting a side all in-cell If and vlookup statements - and so on, I have got pretty far in a project that I am working on, but have hit one small snag to complete it.

From initial data input to Macro's that do collect data in one sheet, then after print - harvests this data and then sorts in to a new order within a "Transfer" Sheet, it's ready to move to the last sheet.

>Bridging a gap!

So far everything works based on my novice status

I need to be able to get one small piece of code together that now accesses the "Transfer" sheet, finds the date ("B6" [e.g. June-16]) and matches it against a corresponding one in another sheet "Annual" in row("B6:M6" [Jan-16:Dec-16]). The result would be;

Sub: on macro print() Do other work, then, lookup "Transfer"("B6"), lookup "Annual"("B6:M6"), "Transfer"("B6")= "Annual"("G6"), then copy content "Transfer"("B9:B128"), paste "Annual"(G9:G128"). End

However, I'm not sure though, I think some of my problem is based on the level of automation the workbook is working on, for example: Transfer (B6) and Annual (B6:K6) dates are based on differing formulas (variables) resulting in the same (values [Jun-16 // mmm-yy])

Your help would be greatly appreciated

JohnH

JLGWhiz · Aug 14, 2016

I think I parsed out what you want to do. You can try this to see if it will work with the date formats.

Code:

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range
Set sh1 = Sheets("Transfer")
Set sh2 = Sheets("Annual")
Set fn = sh2.Range("B6:M6").Find(sh1.Range("B6").Text, , xlValues)
    If Not fn Is Nothing Then
        sh1.Range("B9:B128").Copy sh2.Range("G9")
    End If
Set fn = Nothing
End Sub

JohnH76 · Aug 15, 2016

Hi JLGWhiz,

Thanks for your answer, and it did in fact come quite close; so nice try

The unfortunate element that knocked it over was >> .Copy sh2.Range("G9") part.

The reason for this is, when implementing it, I found that this section of the code has fixed itself to the one target cell "Annual"("G9") and it needs to roam and search for the right [corresponding date].

That is, and as an acid test, I changed the transferring date "Transfer" from June-16 to September-16... and it overwrote June-16 data in "Annual". In-turn, would keep doing this time over based on that the code is missing the "find right date" within the range ("B6:M6") and copy/paste to the corresponding column, as per example.

Again, thanks JLGWhiz very close.

JLGWhiz · Aug 15, 2016

I have no idea what I was thinking when I put the Range("G9") in there. This should do what you want.

Code:

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range
Set sh1 = Sheets("Transfer")
Set sh2 = Sheets("Annual")
Set fn = sh2.Range("B6:M6").Find(sh1.Range("B6").Text, , xlValues)
    If Not fn Is Nothing Then
        sh1.Range("B9:B128").Copy fn.Offset(3)
    End If
Set fn = Nothing
End Sub

JohnH76 · Aug 15, 2016

Not to worry, these things happen!

It does do what it says on the tin... Great stuff and greatly appreciated JLGWhiz, because there's no rating system as such: one on me

Kind Regards

John

JLGWhiz · Aug 15, 2016

JohnH76 said:
Not to worry, these things happen!

It does do what it says on the tin... Great stuff and greatly appreciated JLGWhiz, because there's no rating system as such: one on me

Kind Regards

John

glad you could use it,
Regards, JLG

VBA Macro to compare two dates in two sheets, then transfer relevant data to relevant column

JohnH76

New Member

Excel Facts

JLGWhiz

Well-known Member

JohnH76

New Member

JLGWhiz

Well-known Member

JohnH76

New Member

JLGWhiz

Well-known Member

Similar threads

Forum statistics

Share this page

VBA Macro to compare two dates in two sheets, then transfer relevant data to relevant column

JohnH76

New Member

Excel Facts

JLGWhiz

Well-known Member

JohnH76

New Member

JLGWhiz

Well-known Member

JohnH76

New Member

JLGWhiz

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock