Eric Penfold
Active Member
- Joined
- Nov 19, 2021
- Messages
- 424
- Office Version
- 365
- Platform
- Windows
- Mobile
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
Look forward to any help
VBA 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 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