Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter and Delete Picture
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter and Delete Picture
Wiwi wrote:
With WS_OrderSummary Selection.AutoFilter Field:=2, Criteria1:=Style Selection.AutoFilter Field:=5, Criteria1:=Buyer Selection.AutoFilter Field:=6, Criteria1:=Agent ActiveCell.EntireRow.Delete '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).Count1 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter and Delete Picture
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I can't delete a picture | Excel Discussion (Misc queries) | |||
delete picture with macro | Excel Discussion (Misc queries) | |||
DELETE A PICTURE | Excel Discussion (Misc queries) | |||
How do I delete Word Picture with macro | Excel Programming | |||
delete word picture with macro? | Excel Programming |