تفضل اخي ضع هدا في موديول
Sub GetPrice2()
Dim WSPrice As Worksheet, dest As Worksheet, ws As Worksheet, WSitems As Worksheet
Dim LASTROW&, Dest_Last&, Cpt&, DataRow&, destRow&, I&, derlig&, Z&
Dim Clé As Object, dictKey As String, Price_list As String
Dim srcRng As Range, KeyRng As Range, Dest_Rng As Range
Dim Col As Variant, f As Variant, Réf As Variant
Dim ShtDate As Date, MaxDate As Date, Title As Range
Dim XPric As Range, XROW As Range, S As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
Set dest = Worksheets("itemout")
Set WSitems = ThisWorkbook.Sheets("items")
Set XPric = dest.[E4]: Set Title = dest.[B8:B32]: Price_list = dest.[B4].Value
If Price_list = "" Then: MsgBox "يجب عليك إدخال التاريخ", vbInformation: Exit Sub
If XPric = "" Then: MsgBox "يجب عليك إدخال نوع التعامل", vbInformation: Exit Sub
If Len(Price_list) > 0 Then
If IsDate(dest.Range("B4").Value) Then
For Each ws In Worksheets
If IsDate(ws.Name) Then
ShtDate = CDate(ws.Name)
If ShtDate <= Price_list And ShtDate > MaxDate Then MaxDate = ShtDate
End If
Next ws
If MaxDate = 0 Then
MsgBox "قائمة الأسعار " & Price_list & _
vbCrLf & vbCrLf & "غير موجودة", _
vbInformation, "التحقق من قوائم الأسعار"
Else
Set WSPrice = Sheets(Format(MaxDate, "dd-m-yyyy"))
With WSPrice
If WSPrice.FilterMode Then WSPrice.ShowAllData
DataRow = 5
LASTROW = .Range("D" & .Rows.Count).End(xlUp).Row
Set srcRng = .Range(.Cells(DataRow, "D"), .Cells(LASTROW, "J"))
Col = srcRng.Value2
End With
For Z = 8 To 32
Union(dest.Range("A" & Z), dest.Range("C" & Z), dest.Range("G" & Z), dest.Range("H" & Z)).ClearContents
Next Z
With dest
destRow = 8
Dest_Last = .Range("B" & .Rows.Count).End(xlUp).Row
Set KeyRng = .Range(.Cells(destRow, "B"), .Cells(Dest_Last, "F"))
f = KeyRng.Value2: Set Dest_Rng = .Cells(destRow, "G")
ReDim Réf(1 To UBound(f, 1), 1 To 1)
End With
Set Clé = CreateObject("Scripting.dictionary")
For I = 1 To UBound(Col)
dictKey = Col(I, 1)
If Not Clé.exists(dictKey) And (dictKey) <> "" Then
Clé(dictKey) = I
End If
Next I
For I = 1 To UBound(f)
dictKey = f(I, 1)
If Clé.exists(dictKey) Then
Cpt = Clé(dictKey)
Set XROW = WSPrice.Rows(3).Find(What:=XPric, LookIn:=xlValues, LookAt:=xlWhole)
If Not XROW Is Nothing Then
For Frow = 8 To dest.Range("B" & Rows.Count).End(xlUp).Row
Set B = WSitems.Cells.Find(What:=dest.Range("B" & Frow), LookAt:=xlPart)
If Not B Is Nothing And B <> "" Then dest.Range("C" & Frow) = B.Offset(0, 1).Value
Next Frow
Réf(I, 1) = WSPrice.Cells(Cpt + 4, XROW.Column)
Else
MsgBox "نوع التعامل غير موجود"
Exit Sub
End If
End If
On Error Resume Next
Next I
Dest_Rng.Resize(UBound(Réf, 1), UBound(Réf, 2)) = Réf
End If
End If
End If
For Each S In Title
If S.Value <> "" Then
J = J + 1
S.Offset(0, -1).Value = Format(J, "0")
End If
Next
derlig = dest.Range("B" & dest.Rows.Count).End(xlUp).Row
With dest.Range("H8:H" & derlig)
.Formula = "=IF(F8<>"""",F8*G8,"""")"
.Value = .Value
End With
.EnableEvents = True
.ScreenUpdating = True
dest.[i1] = "اسعار قائمة" & ":" & WSPrice.Name
End With
End Sub
وهدا في حدث ورقة itemout
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Target.Worksheet.Range("E4")) Is Nothing Then
If Target.Cells.Value = " " Or IsEmpty(Target) Then Exit Sub
Application.EnableEvents = False
Call GetPrice2
End If
If Intersect(Target, Range("B8:B32,F8:F32,H8:H32")) Is Nothing Then Exit Sub
Call GetPrice2
Application.EnableEvents = True
On Error GoTo 0
End Sub
price list officena V3.xlsm