Sub OverallStockPosition()
importStockPos
Sheet1.Select
Worksheets("Main Data").Range("A2").Formula = "=(IFERROR(VLOOKUP(J2,'SKU Input'!A:B,2,0),""NO""))"
Worksheets("Main Data").Range("B2").Formula = "=IFERROR(VLOOKUP(J2,'SKU Input'!D:E,2,0),""NO"")"
Worksheets("Main Data").Range("C2").Formula = "=IF(AND(B2=""YES"",AA2=""YES""),""YES"",""NO"")"
Worksheets("Main Data").Range("D2").Formula = "=IF(AND(A2=""YES"",AA2=""YES""),""YES"",""NO"")"
Worksheets("Main Data").Range("E2").Formula = "=IFNA(IF(VLOOKUP(J2,'SQL 0001 INPUT'!A:F,6,0)<0,0,VLOOKUP(J2,'SQL 0001 INPUT'!A:F,6,0)),0)"
Worksheets("Main Data").Range("F2").Formula = "=IFNA(IF(VLOOKUP(J2,'SQL 0005 INPUT'!A:F,6,0)<0,0,VLOOKUP(J2,'SQL 0005 INPUT'!A:F,6,0)),0)"
Worksheets("Main Data").Range("G2").Formula = "=IF(F2<=Q2,F2,Q2)"
Worksheets("Main Data").Range("H2").Formula = "=IF(E2<=Q2,E2,Q2)"
Worksheets("Main Data").Range("I2").Formula = "=IF(SUM(AC2:AF2)>0,""YES"",""NO"")"
Range("a2:i2").Select
Selection.Copy
Range("J1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -9).Select
Range(Selection, Selection.End(xlUp).Offset(1, 0)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("a2:i2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
'''''
On Error GoTo nextfilter
Worksheets("Main Data").Range("a1").AutoFilter Field:=3, Criteria1:="YES"
Range("B1").Select
Selection.End(xlDown).Select
ActiveCell.Value = "NO"
ActiveCell.Copy
Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
ActiveSheet.Paste
Range("a1").AutoFilter
nextfilter:
'''''
On Error GoTo nextfilter2
Range("a1").AutoFilter
Worksheets("Main Data").Range("a1").AutoFilter Field:=4, Criteria1:="YES"
Range("A1").Select
Selection.End(xlDown).Select
If IsEmpty(ActiveCell) = False Then
ActiveCell.Value = "NO"
ActiveCell.Copy
Range(Selection, Selection.End(xlUp)).Offset(1, 0).Select
ActiveSheet.Paste
Range("a1").AutoFilter
Else
Range("a1").AutoFilter
End If
nextfilter2:
Range("a1").AutoFilter
'''''
Sheet3.Select
ptRefresh
End Sub
Thanks!
Bookmarks