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: 8

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
What happens if you change
VBA Code:
.Range("B1").Value
to
VBA Code:
Clng(.Range("B1").Value)
 
Upvote 0
On sheet ProtuctA in cell B1 has a date or text?
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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
Back
Top