Filter and Delete Picture

W

wiwi

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
 
B

Bill Manville

Wiwi said:
With WS_OrderSummary
Selection.AutoFilter Field:=2, Criteria1:=Style
Selection.AutoFilter Field:=5, Criteria1:=Buyer
Selection.AutoFilter Field:=6, Criteria1:=Agent
ActiveCell.EntireRow.Delete
Click to expand...

'Firstly, Selection may not be on WS_OrderSummary or in the table you
want to filter. so

With WS_OrderSummary.Range("A1").CurrentRegion
.AutoFilter ' remove any previous filter
.AutoFilter Field:=2, Criteria1:=Style
.AutoFilter Field:=5, Criteria1:=Buyer
.AutoFilter Field:=6, Criteria1:=Agent

'Secondly, to determine whether the filter found any rows, and to
delete such rows:
If .Columns(1).SpecialCells(xlVisible).Count>1 Then
' not just header row visible - delete visible data row(s)
.Offset(1).Resize(.Rows.Count-1).SpecialCells(xlVisible). _
EntireRow.Delete
End If
End With

Hope this helps

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup
 
W

wiwi

Thanks Bill. I tried the code as your advice, but It doesn't work.
I entry the same data and save it, and didn't delete the same data.
was run the debug and show me skip in part 'delete if found in filter'
Below is the code that I done. Please help and advice ..Thanks i
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("orde
confirmation.xls")
'Set Wkbk_Summary = Workbooks.Open(Filename:="L:\Sales
Marketing\Marketing Costing\Order Summary\Order Summary.xls")
Set Wkbk_Summary = Workbooks.Open(Filename:="C:\Documents an
Settings\default\Desktop\Order Summary.xls")
Set WS_OrderConfirm = wkbk_OrderConfirm.Worksheets("Order Confirmation
(2)")
Set WS_OrderSummary = Wkbk_Summary.Worksheets("Order Summary")
Set WS_OrderSummaryPics = Wkbk_Summary.Worksheets("PICS")

Application.ScreenUpdating = False

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

WS_OrderSummary.Activate

With WS_OrderSummary.Range("A1").CurrentRegion
.AutoFilter ' remove any previous filter
.AutoFilter Field:=2, Criteria1:=Style
.AutoFilter Field:=5, Criteria1:=Buyer
.AutoFilter Field:=6, Criteria1:=Agent

'delete if found in filter
If .Columns(1).SpecialCells(xlVisible).Count > 1 Then
' not just header row visible - delete visible data row(s)
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible). _
EntireRow.Delete
Else
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
End If
End With

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

WS_OrderSummary.Activate
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

With WS_OrderSummary
LastRow = Application.CountA(.Range("B:B")) + 1
.Cells(LastRow, 1).Select
.Paste
Application.ScreenUpdating = True
Wkbk_Summary.Close savechanges:=True

End Su
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

save data , images and hyperlink between two workbooks 2
autofill to visible cells only 5
Select rows equal to filter and highlight row 4
Autofilter and Cell Count 1
Stop looping macro 1
Making Code More Efficient 6
Combining two different subs w/ similar variables 10
Runs once and only once 1

Top