Hsieh

1. Sub Ex()
   
2.     With ThisWorkbook.Sheets("BCM±±ºÞ")
   
3.     .Columns("A:CZ").Hidden = False
   
4. 
   
5.     .Range("A:CZ").AutoFilter Field:=70, Criteria1:=Array( _
   
6.         "Delay", "OK", "pre-Booking"), Operator:=xlFilterValues
   
7.          
   
8.    Set a = Intersect(.UsedRange, .Range("E:BW")).SpecialCells(xlCellTypeVisible)
   
9.    With Workbooks.Add
   
10.    With .Sheets(1)
    
11.     a.Copy .[A1]
    
12.     .Columns("BO:BQ").Delete Shift:=xlToLeft
    
13.     Columns("BD:BM").Delete Shift:=xlToLeft
    
14.     Columns("AX:AX").Delete Shift:=xlToLeft
    
15.     Columns("AM:AU").Delete Shift:=xlToLeft
    
16.     Columns("AA:AA").Delete Shift:=xlToLeft
    
17.     Columns("V:W").Delete Shift:=xlToLeft
    
18.     Columns("S:T").Delete Shift:=xlToLeft
    
19.    End With
    
20.    '.SaveAs "D:\¥t¦s·sÀÉ.xlsx"   '¦¹³BÀx¦sÀÉ®×
    
21.    End With
    
22.    End With
    
23. End Sub

½Æ»s¥N½X

¦^´_ 6# PJChen

Hsieh

¦^´_ 9# PJChen
§Úªº¼gªk¤]¬OVBA»y¨¥
VBA¬OVBªº°ª¶¥»y¨¥¡A»yªk¬Û¦P¡A¥u¬OVBA¤w¸g¦³¤F³\¦hª«¥ó»P¼Ò²Õ¨Ñ¨Ï¥Î
¦Û¾ÇVBA·íµM¤£¬O°ÝÃD¡A¥u¬O¿ý»s©Ò±oªºµ{¦¡½X·|±N©Ò¦³°Ê§@³£°O¿ý¶i¥h¡A³y¦¨³\¦h¤£¥²­nªºµ{¦¡½X
§AÀ³°t¦XF1»¡©ú¨Ó¤F¸Ñ¨C­Ó«ü¥O¡A»Pµ{¦¡±Ô­zªº·N¸q¡A¦A¦h¬Ý§O¤Hªº¼gªk¡A¥[¥H«ä¦Ò¨äÅÞ¿è
µ{¦¡½X¤¤a.Copy .[A1]
³o¬O±Nª«¥óÅܼÆa½Æ»s¨ì·s¬¡­¶Ã¯ªº²Ä¤@­Ó¤u§@ªíªºA1Àx¦s®æªº·N«ä
¦Ü©ó·sÀÉ®×¥u­n¼g¤J­È¤ÎÅã¥Ü¥þ³¡¸ê®Æ½Ð°Ñ¦Ò¥H¤Uµ{¦¡½X

1. Sub Ex()
   
2. Dim A As Range
   
3.     With ThisWorkbook.Sheets("BCM±±ºÞ")
   
4.     .Columns("A:CZ").Hidden = False
   
5. 
   
6.     .Range("A:CZ").AutoFilter Field:=70, Criteria1:=Array( _
   
7.         "Delay", "OK", "pre-Booking"), Operator:=xlFilterValues
   
8.          
   
9.    Set A = Intersect(.UsedRange, .Range("E:BW")).SpecialCells(xlCellTypeVisible)
   
10.    With Workbooks.Add
    
11.    With .Sheets(1)
    
12.    'A.Copy .Range("A1")  '§¹¥þ½Æ»s
    
13.      A.Copy
    
14.     .Range("A1").PasteSpecial xlPasteValues '¿ï¾Ü©Ê¶K¤W­È
    
15.    
    
16.     .Columns("BO:BQ").Delete Shift:=xlToLeft
    
17.     .Columns("BD:BM").Delete Shift:=xlToLeft
    
18.     .Columns("AX:AX").Delete Shift:=xlToLeft
    
19.     .Columns("AM:AU").Delete Shift:=xlToLeft
    
20.     .Columns("AA:AA").Delete Shift:=xlToLeft
    
21.     .Columns("V:W").Delete Shift:=xlToLeft
    
22.     .Columns("S:T").Delete Shift:=xlToLeft
    
23.    End With
    
24.    '.SaveAs "D:\¥t¦s·sÀÉ.xlsx"   '¦¹³BÀx¦sÀÉ®×
    
25.    End With
    
26.    .ShowAllData 'Åã¥Ü©Ò¦³¸ê®Æ
    
27.    End With
    
28. End Sub

½Æ»s¥N½X

¶K¤Wµ{¦¡½X­n¥[¤J¦æ¸¹¡A¦p¹Ï¾Þ§@

¤U¸ü (117.96 KB)

2012-1-19 10:58

Hsieh

¦^´_ 12# PJChen


    With ThisWorkbook.Sheets("Chart-F")
    Set B = Intersect(.UsedRange, .Range("A:D")).SpecialCells(xlCellTypeVisible)
    B.Copy Workbooks("2011 BCMart Multi-Format.xlsx").Sheets("Factory's order").Range("A1")      '§¹¥þ½Æ»s
   End With

Hsieh

¦^´_ 14# PJChen

With ThisWorkbook.Sheets("Chart-F")
    Set B = Intersect(.UsedRange, .Range("A:D")).SpecialCells(xlCellTypeVisible)
    Workbooks("2011 BCMart Multi-Format.xlsx").Activate
    Sheets("Factory's order").Range("A1").Select
    B.Copy
    Selection.PasteSpecial xlPasteValues   '¿ï¾Ü©Ê¶K¤W­È
End With