VBA Copy/Paste By Date Lookup

amandakay740

New Member
Joined
Jul 8, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
I'm attempting to paste the data from one workbook to another based on date (I've included a screenshot below as an example):

Left screenshot (source workbook):
No code help needed but a rough example of what I would use is:

Set x = Workbook 3 (source input)
Set y = Workbook 4 (source output)

x.Sheets("ProductA").Range("B3").Copy


Right Screenshot (source output)
I'd like to paste cell B3 from the source workbook into cell B7 of the source output by using the date reference. I do not want to use a last row formula because sometimes this row will be input manually before the Macro is run, so I'd like it to reference the date in the source workbook and drop it in the appropriate row of the source output workbook.

Thank you for any assistance you may have!
 

Attachments

  • Date_Lookup_VBA_071220.PNG
    Date_Lookup_VBA_071220.PNG
    110.7 KB · Views: 6

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,620
Office Version
  1. 2007
Platform
  1. Windows
Try this

VBA Code:
Sub test_2()
  Dim x As Workbook, y As Workbook
  Dim f As Range
  
  Set x = Workbooks("book3")  'source input
  Set y = Workbooks("book4")  'source output
  
  With x.Sheets("ProductA")
    Set f = y.Sheets("Annual_Summary").Range("A:A").Find(.Range("B1").Value, , xlFormulas, xlWhole)
    If Not f Is Nothing Then
      f.Offset(, 1).Value = .Range("B3").Value
      f.Offset(, 2).Value = .Range("B4").Value
      f.Offset(, 3).Value = .Range("B5").Value
    Else
      MsgBox "There is no date"
    End If
  End With
End Sub
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,049
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What happens if you change
VBA Code:
.Range("B1").Value
to
VBA Code:
Clng(.Range("B1").Value)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,620
Office Version
  1. 2007
Platform
  1. Windows
On sheet ProtuctA in cell B1 has a date or text?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top