Error 400 in VBA to copy data between workbooks

[aggiemarine07]

So I am banging my head against a wall because I keep getting the helpful "Error 400" box but obviously its not showing me where my code is failing. What I am essentially trying to do is copy data from one workbook to another by inserting a formula to pull it from one workbook into the other. Can anyone help me out? I think I have just been staring at it too long and cant see the issue.

**I have tested for the array formula independently of the below code and can confirm that it works

Public Sub Update_PLOGs()

'-update files within PLOG's--index/match
'------------->
Dim wkb1 As Workbook
Dim sht1 As Worksheet
Dim wkb2 As Workbook
Dim sht2 As Worksheet
Dim wkb3 As Workbook
Dim sht3 As Worksheet
Dim wkb4 As Workbook
Dim sht4 As Worksheet


Set wkb1 = ThisWorkbook
Set wkb2 = Workbooks.Open(ThisWorkbook.Path & "\plogs\PLOG - Alpenrose - * - (DAI).xlsb")
Set wkb3 = Workbooks.Open(ThisWorkbook.Path & "\plogs\PLOG - Alta Dena - * - (DAI).xlsb")
Set wkb4 = Workbooks.Open(ThisWorkbook.Path & "\plogs\PLOG - Clover - * - (DAI).xlsb")

Set sht1 = wkb1.Sheets("Cost Change Tracker")
Set sht2 = wkb2.Sheets("WFM PLOG")
Set sht3 = wkb3.Sheets("WFM PLOG")
Set sht4 = wkb4.Sheets("WFM PLOG")

    '------Update Alpenrose----adjust this range if items are added or removed from PLOG
    sht2.Range("FH2").FormulaArray = "=INDEX('[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$A$1:$CF$165,MATCH(1,(LEFT(FH1,2)='[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$CF$1:$CF$165)*(B2='[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$C$1:$C$165),0),MATCH(CONCAT(UPPER(TEXT(DATE(2015,MONTH(TODAY())+1,1),""mmmm"")),"" Delivered / FOB Case Cost""),'[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$A$1:$CF$1,0))"
    sht2.Range("FH2:FH13").FillDown
    sht2.Range("FH2:FH13").Copy
    sht2.Range("FH2:FH13").PasteSpecial Paste:=xlPasteValues
    sht2.Range("IJ2:IJ13").Value = Date
    sht2.Range("IK2:IK13").Value = "COST: Cost Change as part of monthly federal milk order update."
   
    '------Update Alta Dena----adjust this range if items are added or removed from PLOG
    'SP (CALIFORNIA)
    sht3.Range("EL2").FormulaArray = "=INDEX('[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$A$1:$CF$165,MATCH(1,(LEFT(FH1,2)='[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$CF$1:$CF$165)*(B2='[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$C$1:$C$165),0),MATCH(CONCAT(UPPER(TEXT(DATE(2015,MONTH(TODAY())+1,1),""mmmm"")),"" Delivered / FOB Case Cost""),'[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$A$1:$CF$1,0))"
    sht3.Range("EL2:EL9").FillDown
    sht3.Range("EL2:EL9").Copy
    sht3.Range("EL2:EL9").PasteSpecial Paste:=xlPasteValues
    'SP (NV AND AZ)
    sht3.Range("GL2").FormulaArray = "=INDEX('[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$A$1:$CF$165,MATCH(1,(LEFT(FH1,2)='[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$CF$1:$CF$165)*(B2='[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$C$1:$C$165),0),MATCH(CONCAT(UPPER(TEXT(DATE(2015,MONTH(TODAY())+1,1),""mmmm"")),"" Delivered / FOB Case Cost""),'[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$A$1:$CF$1,0))"
    sht3.Range("GL2:GL9").FillDown
    sht3.Range("GL2:GL9").Copy
    sht3.Range("GL2:GL9").PasteSpecial Paste:=xlPasteValues
    sht3.Range("IJ2:IJ9").Value = Date
    sht3.Range("IK2:IK9").Value = "COST: Cost Change as part of monthly federal milk order update."

    '------Update Clover----adjust this range if items are added or removed from PLOG
    sht4.Range("EX2").FormulaArray = "=INDEX('[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$A$1:$CF$165,MATCH(1,(LEFT(FH1,2)='[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$CF$1:$CF$165)*(B2='[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$C$1:$C$165),0),MATCH(CONCAT(UPPER(TEXT(DATE(2015,MONTH(TODAY())+1,1),""mmmm"")),"" Delivered / FOB Case Cost""),'[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$A$1:$CF$1,0))"
    sht4.Range("EX2:EX9").FillDown
    sht4.Range("EX2:EX9").Copy
    sht4.Range("EX2:EX9").PasteSpecial Paste:=xlPasteValues
    sht4.Range("IJ2:IJ9").Value = Date
    sht4.Range("IK2:IK9").Value = "COST: Cost Change as part of monthly federal milk order update."

   
   
   
    wkb1.Save
    wkb2.Save
    wkb3.Save
    wkb4.Save
    wkb1.Close True
    wkb2.Close True
    wkb3.Close True
    wkb4.Close True
    '---------------------

MsgBox "All files updated! If there are other updates, please remember to do those manually."
End Sub

 

[CountTepes]

After your Dim statements put in the code

On Error Goto ErrorTrap

Then just before End Sub put in

Exit Sub
:ErrorTrap
msgbox Err.Description

This will tell you how the code was failing.

If you still cannot work it out from that, put a break in the code at the first Set statment. Then using F9 step through. That will tell you where it is failing.

 

[aggiemarine07]

ok i put that in, much more helpful; thanks! Now it reads "Method open of object workbooks failed"

so i changed my code to this but it still gives the same error:

Public Sub Update_PLOGs()

'-update files within PLOG's--index/match
'------------->
Dim wkb1 As Workbook
Dim sht1 As Worksheet
Dim wkb2 As Workbook
Dim sht2 As Worksheet
Dim sFound2 As String

On Error GoTo ErrorTrap

Set wkb1 = ThisWorkbook
sFound2 = Dir(ThisWorkbook.Path & "\plogs\PLOG - Alpenrose - * - (DAI).xlsb")    'the first one found
If sFound2 <> "" Then
    Workbooks.Open (ThisWorkbook.Path & "\" & sFound2)
End If

'Set sht1 = wkb1.Sheets("Cost Change Tracker")
'Set sht2 = wkb2.Sheets("WFM PLOG")


    '------Update Alpenrose----adjust this range if items are added or removed from PLOG
    sht2.Range("FH2").FormulaArray = "=INDEX('[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$A$1:$CF$165,MATCH(1,(LEFT(FH1,2)='[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$CF$1:$CF$165)*(B2='[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$C$1:$C$165),0),MATCH(CONCAT(UPPER(TEXT(DATE(2015,MONTH(TODAY())+1,1),""mmmm"")),"" Delivered / FOB Case Cost""),'[Monthly Milk Cost Tracker v2.0.xlsb]Cost Change Tracker'!$A$1:$CF$1,0))"
    sht2.Range("FH2:FH13").FillDown
    sht2.Range("FH2:FH13").Copy
    sht2.Range("FH2:FH13").PasteSpecial Paste:=xlPasteValues
    sht2.Range("IJ2:IJ13").Value = Date
    sht2.Range("IK2:IK13").Value = "COST: Cost Change as part of monthly federal milk order update."
   
    
'    wkb1.Save
'    wkb2.Save
'    wkb1.Close True
'    wkb2.Close True
    '---------------------
MsgBox "All files updated! If there are other updates, please remember to do those manually."


ErrorTrap:
MsgBox Err.Description
End Sub

 

[CountTepes]

Try using this format of open

Workbooks.Open Filename:=PathName & Filename

See if it makes a difference.