Find sheet then add vlookup value from mastersheet

[Eric Penfold]

The VLookup does not work. Should take value from "purchaseanalysis" sheet on to any sheets that match with the column A value. Says range of object global failed?
Look forward to any help

Sub vLookupWorkbookSheets()

    Dim Src As Workbook
    Dim Des As Workbook
    Dim FileToOpen As Variant
    Dim SName As Variant
    Dim MyArray As Variant
    Dim MyDate As String
    Dim LRow1 As Long
    Dim LRow2 As Long
    Dim ColNum As String
    Dim I As Long
    Dim x As Long
    Dim PurAnalysis As ListObject
    Dim WS As Worksheet
    
    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With

    FileToOpen = ("\\DC01\Company\PURCHASING\Forecasting\Brett Martin Template for Vlookup.xlsm")
    Workbooks.Open FileToOpen
    Set Src = Workbooks("Brett Martin Template for Vlookup.xlsm")
    Set Des = Workbooks("Brett Martin Forecast 2022.xlsm")
    Set SASheet = Src.Sheets("Sales Analysis")
    Set PurAnalysis = Des.Sheets("PurchasingAnalysis").ListObjects("Purchasing_Analysis")
    


    LCol = PurAnalysis.Range.Columns.Count

    
       LastMonth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmmm")
            
       MyDate = LastMonth
       ColNum = Month(DateValue("01-" & MyDate & "-1900"))
       ColNum = ColNum + 1

      On Error Resume Next
      
        MyArray = PurAnalysis.DataBodyRange
     
        LRow1 = PurAnalysis.DataBodyRange.Rows.Count
        
        
        For Each WS In Des.Worksheets
        
        LRow2 = WS.Cells(WS.Rows.Count, 4).End(xlUp).Row + 1
        
        For x = LBound(MyArray) To UBound(MyArray)
        SName = MyArray(x, 1)
        
        If SName = WS Then
        
        WS("D" & LRow2).Value = WorksheetFunction.VLookup(Range(2, ColNum).Value, Range("Purchasing_Analysis"), 4, 0)
        
        End If
        
        Next x
        Next WS
        
        Workbooks("Brett Martin Template for Vlookup.xlsm").Close _
        SaveChanges:=False

         With Application
        
        .ScreenUpdating = True
        .EnableEvents = True
        End With
    
    End Sub

 

[Eric Penfold]

Sorry forgot to loop the ws this is revised code

Sub vLookupWorkbookSheets()

    Dim Src As Workbook
    Dim Des As Workbook
    Dim FileToOpen As Variant
    Dim SName As Variant
    Dim MyArray As Variant
    Dim MyDate As String
    Dim LRow1 As Long
    Dim LRow2 As Long
    Dim i As Long
    Dim x As Long
    Dim PurAnalysis As ListObject
    Dim WS As Worksheet
    
    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With

    FileToOpen = ("\\DC01\Company\PURCHASING\Forecasting\Brett Martin Template for Vlookup.xlsm")
    Workbooks.Open FileToOpen
    Set Src = Workbooks("Brett Martin Template for Vlookup.xlsm")
    Set Des = Workbooks("Brett Martin Forecast 2022.xlsm")
    Set PurSheet = Des.Sheets("PurchasingAnalysis")
    Set PurAnalysis = Des.Sheets("PurchasingAnalysis").ListObjects("Purchasing_Analysis")
    
       LastMonth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmmm")
            
       MyDate = LastMonth
       ColNum = Month(DateValue("01-" & MyDate & "-1900"))
       ColNum = ColNum + 1

      On Error Resume Next
      
        MyArray = PurAnalysis.DataBodyRange
     
        LRow1 = PurAnalysis.DataBodyRange.Rows.Count
        
        
        For Each WS In Des.Worksheets
        
        LRow2 = WS.Cells(WS.Rows.Count, 4).End(xlUp).Row + 1
        
        For x = LBound(MyArray) To UBound(MyArray)
        SName = MyArray(x, 1)
        
        

        For i = 2 To LRow1
        
        If SName = WS Then
        
        WS("D" & LRow2).Value = WorksheetFunction.VLookup(Range(i, ColNum).Value, Range("Purchasing_Analysis"), 4, 0)
        
        End If
        
        Next i
        Next x
        Next WS

        
        Workbooks("Brett Martin Template for Vlookup.xlsm").Close _
        SaveChanges:=False

         With Application
        
        .ScreenUpdating = True
        .EnableEvents = True
        End With
    
    End Sub