Match dates from different worksheets

[Wheelie686]

The following code works fine to match a date on Sheet2 with the date in Sheet1!A1 and paste accordingly. However I tried to modify it so the paste destination is on another workbook but nothing seems to happen.

Sub test()
    Dim x
    x = [match(Sheet1!A1,Sheet2!1:1,0)]
    If IsNumeric(x) Then Sheets("Sheet2").Cells(6, x).Resize(4).Value = Sheets("Sheet1").[A2:A5].Value
End Sub

Sub test2()
    Dim x
    Dim Calc As Workbook
    Dim Why As Workbook
    
    Set Calc = ThisWorkbook
    Set Why = Workbooks("Book2")
    
    x = [match(Calc.Sheet1!A1,Why.Sheet2!1:1,0)]
    If IsNumeric(x) Then Why.Sheets("Sheet2").Cells(6, x).Resize(4).Value = Calc.Sheets("Sheet1").[A2:A5].Value
End Sub

Does anybody know what I've done wrong in Test2

 

[StephenCrump]

Your code is asking Excel to evaluate a formula containing Calc.Sheet1 and Why.Sheet2.

Both mean nothing to Excel, hence the line will error, x will take an error value (it's a Variant data type by default), and IsNumeric(x) will be FALSE.

Here's one way you could get it to work: x = [match(Sheet1!A1,[Book2]Sheet2!1:1,)]

 

[Wheelie686]

yeah I set Calc and Why as variables. The function of Book1 is to be a calculator for some work stuff and Why because Why is the name of another document but to keep it simple, I just called them Book1 and Book2.

I appreciate the help since it now does exactly what I want. However I'm now stuck with the name Book2. Where in the code would I change the name? I know the name and would never change, it's just Book1 that has a variable name. I've changed it a few times and it always works but Book2 has to be Book2.

Sub test3()
    Dim x
    Dim Calc As Workbook
    Dim Why As Workbook
    
    Set Calc = ThisWorkbook
    Set Why = Workbooks("Book2")
    
    x = [match(Sheet1!A1,[Book2]Sheet2!1:1,)]
    If IsNumeric(x) Then Why.Sheets("Sheet2").Cells(6, x).Resize(4).Value = Calc.Sheets("Sheet1").[A2:A5].Value
End Sub

 

[Alex Blakenburg]

You would need to change Book2 to the new name wherever it appears and so in both these lines.

    Set Why = Workbooks("Book2")
    
    x = [match(Sheet1!A1,[Book2]Sheet2!1:1,)]