I have two workbooks (Order Confirmation) and (Order Summary).
When I add a new record on Order Confirmation form, there will
automatically help to add to Order Summary. Before add the record, will
check in Order Summary
if the record is already exits then delete
else add the record

I'm try to use Filter to find the data, if found then delete the record
Else add the data.
My problem are :
1. How to check (in vba) if the filter is success
2. How to delete the row
3. How to delete the entire picture on the row

Below is my vba code. The filter part is not work. Please help and
advice. Thanks very much in advance


Sub dataimage()

Dim WS_OrderConfirm As Worksheet
Dim WS_OrderSummary As Worksheet
Dim WS_OrderSummaryPics As Worksheet
Dim Wkbk_Summary As Workbook
Dim wkbk_OrderConfirm As Workbook

Dim LastRow
Dim myPict As Picture
Dim newPict As Picture

Set wkbk_OrderConfirm = ThisWorkbook 'workbooks("order
confirmation.xls")
Set Wkbk_Summary = Workbooks.Open(Filename:="L:\Sales &
Marketing\Marketing Costing\Order Summary\Order Summary.xls")
Set WS_OrderConfirm = wkbk_OrderConfirm.Worksheets("Order Confirmations
(2)")
Set WS_OrderSummary = Wkbk_Summary.Worksheets("Order Summary")
Set WS_OrderSummaryPics = Wkbk_Summary.Worksheets("PICS")

WS_OrderConfirm.Activate
Style = Range("AJ3")
Buyer = Range("J4")
Agent = Range("J5")

With WS_OrderSummary
Selection.AutoFilter Field:=2, Criteria1:=Style
Selection.AutoFilter Field:=5, Criteria1:=Buyer
Selection.AutoFilter Field:=6, Criteria1:=Agent
ActiveCell.EntireRow.Delete

With WS_OrderConfirm
'Set myPict = .Pictures(1) 'first/only picture on sheet
Set myPict = .Pictures(.Pictures.Count) 'last picture added??
End With
myPict.Copy

With WS_OrderSummaryPics
Paste
Set newPict = .Pictures(.Pictures.Count)
End With

With newPict
'.Name = "abc"
Top = 6
Left = 5
Height = 60
Width = 60
'or hyperlink with the picture????
Parent.Hyperlinks.Add Anchor:=.ShapeRange.Item(1), _
Address:=wkbk_OrderConfirm.FullName
Cut
End With

Application.ScreenUpdating = False

With WS_OrderSummary
LastRow = Application.CountA(.Range("B:B")) + 1
Cells(LastRow, 1).Select
Paste
Cells(LastRow, 2).Value = WS_OrderConfirm.Range("$AJ$3").Value
Cells(LastRow, 3).Value = WS_OrderConfirm.Range("$AJ$5").Value
Cells(LastRow, 4).Value = WS_OrderConfirm.Range("$BJ$4").Value
Cells(LastRow, 5).Value = WS_OrderConfirm.Range("$J$5").Value
Cells(LastRow, 6).Value = WS_OrderConfirm.Range("$J$4").Value
Cells(LastRow, 7).Value = WS_OrderConfirm.Range("$AJ$4").Value
Cells(LastRow, 8).Value = WS_OrderConfirm.Range("$J$6").Value
Cells(LastRow, 9).Value = WS_OrderConfirm.Range("$L$25").Value
Cells(LastRow, 10).Value = WS_OrderConfirm.Range("$AO$25").Value
Cells(LastRow, 11).Value = WS_OrderConfirm.Range("$S$28").Value
Cells(LastRow, 12).Value = WS_OrderConfirm.Range("$L$26").Value
Cells(LastRow, 13).Value = WS_OrderConfirm.Range("$AO$26").Value
Cells(LastRow, 14).Value = WS_OrderConfirm.Range("$DY$3").Value
Cells(LastRow, 15).Value = WS_OrderConfirm.Range("$CG$2").Value
Cells(LastRow, 16).Value = WS_OrderConfirm.Range("$CG$3").Value
Cells(LastRow, 17).Value = WS_OrderConfirm.Range("$AJ$3").Value
Cells(LastRow, 18).Value = WS_OrderConfirm.Range("$DY$5").Value
'hyperlink in column C???
'.Cells(LastRow, 20).Formula = "=hyperlink(" & Chr(34) &
wkbk_OrderConfirm.FullName & Chr(34) & ")"
End With

Application.ScreenUpdating = True
Wkbk_Summary.Close savechanges:=True

End Sub8


---
Message posted from http://www.ExcelForum.com/