ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¦Û°Ê½Æ»s¦³¸ê®Æªº°Ï¶ô

¦Û°Ê½Æ»s¦³¸ê®Æªº°Ï¶ô

Hi,
§Ú¦b¿ý»s¥¨¶°®É,±`·|¥Î¨ì½Æ»s¤@­Ó«ü©w°Ï¶ô¨ì¥t¤@­Ó·sªºsheet¤¤,¨Ò¦pB2:E55,¦ý¦]¬°¸ê®Æ·|¤@ª½¼W¥[,¥i¯à¤U¦^¬OB2:F99,¦ý¦b¿ý¥¨¶°®É§Ú¤S¤£·Q±N½d³ò©Ô±o¤Ó¤j,¯à¤£¯à¦b°õ¦æ¥¨¶°®É,­Y§Ú­nB2:E..(¸ê®Æ³Ì©³ºÝ),¤]¦³¥i¯à¬OC3:Y180,¦]¬°§Ú¦³«Ü¦hsheet­n°õ¦æ,¦ý¨C­Ó¤u§@ªí¤£¦P,©Ò¥H½d³ò¤]³£¤£¤@¼Ë,¦ý°ß¤@¬Û¦Pªº,³£¬O­n¦Ü¸ê®Æªº³Ì©³ºÝ,¥¦¥i¥H¦Û°Ê¿z¿ï¥X¦³¸ê®Æªº°Ï¶ô¶Ü?

ActiveSheet.UsedRange.Copy Sheet3.[A65536].End(3)(2, 1)

TOP

¤j¤j¦n,
§Ú±N§A¦^´_ªº¼gªk®M¨ì§Úªº¥¨¶°¤¤,¦ýµLªk°õ¦æ,Àˬd¤£¥X­þ¸Ì¤£¹ï,§Úcopy¥X¨Ó,¥i§_À°¦£¬Ý¤@¤U?
    Windows("VBA Cluster.xlsm").Activate
    Sheets("BCM±±ºÞ").Select
    Columns("A:CZ").Select
    Selection.EntireColumn.Hidden = False
    ActiveSheet.Range("$A$1CZ$1800").AutoFilter Field:=70, Criteria1:=Array( _
        "Delay", "OK", "pre-Booking"), Operator:=xlFilterValues         
    Range("E1:BW1800").Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Columns("BO:BQ").Select
    Selection.Delete Shift:=xlToLeft
    Columns("BD:BM").Select
    Selection.Delete Shift:=xlToLeft
    Columns("AX:AX").Select
    Selection.Delete Shift:=xlToLeft
    Columns("AM:AU").Select
    Selection.Delete Shift:=xlToLeft
    Columns("AA:AA").Select
    Selection.Delete Shift:=xlToLeft
    Columns("V:W").Select
    Selection.Delete Shift:=xlToLeft
    Columns("S:T").Select
    Selection.Delete Shift:=xlToLeft   
ActiveSheet.UsedRange.Copy SheetsBCM±±ºÞ.[A65536].End(3)(2, 1).Select  =>³o¸ÌµLªk°õ¦æ

    'Range("A1:AQ350").Select
    Selection.Copy
   
    Workbooks.Add
    ActiveSheet.Paste

TOP

¤j®v,¥i§_À°¦£¬Ý¬Ý­þ¸Ì¥X¤F°ÝÃD? §Úªº¤u§@ªí¨C¤Ñ³£·|¼W¥[·sªº¸ê®Æ,§Ú¦bcopy¤u§@ªí¸ê®Æ®É·|°µ¨Ç¿z¿ï¦A¶i¦æcopy..
    Windows("VBA Cluster.xlsm").Activate
    Sheets("BCM±±ºÞ").Select
    Columns("A:CZ").Select
    Selection.EntireColumn.Hidden = False
    ActiveSheet.Range("$A$1CZ$1800").AutoFilter Field:=70, Criteria1:=Array( _
        "Delay", "OK", "pre-Booking"), Operator:=xlFilterValues          '³o¬O¦³¶i¦æ¿z¿ï«áªº
    Range("E1:BW1800").Select    '³o¸Ìªº¸ê®ÆÀH®É·|¼W¥[,¦³¥i¯à¬OE1:BW2000,©ÎªÌ§Oªº¤u§@ªí¤]·|COPY¤£¦Pªº¸ê®Æ,¦³¥i¯à¬OB2:G500,¦]¬°«Ü¦h¤u§@ªí¥Î±o¨ì³o­Ó¥\¯à,§Ú§Æ±æ¦b¶i¦æ¿z¿ï«á,¥uCOPY¦³¸ê®Æªº¦a¤è,¦Ó¤£­n¤j½d³òªºCOPY,·|³y¦¨¤Ó¦h©U§£,«e­±oobird±Ð§Ú¥Î"
ActiveSheet.UsedRange.Copy Sheet3.[A65536].End(3)(2, 1)",¦ý³£µLªk°õ¦æ,½Ð°ª¤âÀ°¦£§ä§ä­ì¦]¥X¦b­þ?

    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
   
ActiveSheet.UsedRange.Copy SheetsBCM±±ºÞ.[A65536].End(3)(2, 1).Select  '§Ú®M¥Î«á,³o¸ÌµLªk°õ¦æ
      
    Workbooks.Add
    ActiveSheet.Paste

TOP

§Ú»Ý­nµ½¤ß¤H¤h¨ÓÀ°¦£,­þ¦ì¤j®v¥i¥HÀ°§Ú¬Ý¬Ý¤W­±ªºµ{¦¡¥X¤F¤°»ò°ÝÃD?±Ï©R!

TOP

[attach]excel[/attach]«e­±ªº»¡©ú+¤W¶ÇÀÉ®×,©Î³\¯à¤ñ¸û©ú¥Õ§Ú¦b»¡¤°»ò?½Ð¤j®aÀ°­Ó¦£!ÁÂÁÂ

VBA Cluster.zip (83.23 KB)

excel

TOP

  1. Sub Ex()
  2.     With ThisWorkbook.Sheets("BCM±±ºÞ")
  3.     .Columns("A:CZ").Hidden = False

  4.     .Range("A:CZ").AutoFilter Field:=70, Criteria1:=Array( _
  5.         "Delay", "OK", "pre-Booking"), Operator:=xlFilterValues
  6.          
  7.    Set a = Intersect(.UsedRange, .Range("E:BW")).SpecialCells(xlCellTypeVisible)
  8.    With Workbooks.Add
  9.    With .Sheets(1)
  10.     a.Copy .[A1]
  11.     .Columns("BO:BQ").Delete Shift:=xlToLeft
  12.     Columns("BD:BM").Delete Shift:=xlToLeft
  13.     Columns("AX:AX").Delete Shift:=xlToLeft
  14.     Columns("AM:AU").Delete Shift:=xlToLeft
  15.     Columns("AA:AA").Delete Shift:=xlToLeft
  16.     Columns("V:W").Delete Shift:=xlToLeft
  17.     Columns("S:T").Delete Shift:=xlToLeft
  18.    End With
  19.    '.SaveAs "D:\¥t¦s·sÀÉ.xlsx"   '¦¹³BÀx¦sÀÉ®×
  20.    End With
  21.    End With
  22. End Sub
½Æ»s¥N½X
¦^´_ 6# PJChen
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ÁÂÁ¶W¯Åª©¥D,¥i¥H¹B§@¤F!¨ü±Ð¤F.:)
§Ú·Q°Ý´X­Ó°ÝÃD: a) ª©¥D¶K¥X¨Óªºµ{¦¡¬°¤°»ò¥ªÃä³£¦³¦æ¸¹,«ç»ò°µªº?   b)¥Î¿ý»sªº¥¨¶°¨äµ{¦¡»y¨¥¬°¤°»ò¸ò§A¼g¥X¨Óªº¤£¤@¼Ë?§A¼g¥X¨Óªºµ{¦¡¤]¥i¥H¦bexcel VBA¤¤¨Ï¥Î,¦ý³o¬O¤°»òµ{¦¡»y¨¥?­þºØµ{¦¡»y¨¥¥i¥H¦@¥Î¦bexcel¤¤?       c) §Ú§¹¥þ¤£·|¼gµ{¦¡,¦pªG§Ú·Q¦Û¾Ç,¤°»ò»y¨¥¬O¤Jªù,¦³¨S¦³±ÀÂ˪º®Ñ?   d)   .Range("A:CZ").AutoFilter Field:=70, Criteria1:=Array( _
        "Delay", "OK", "pre-Booking"), Operator:=xlFilterValues ¬O¦³±ø¥óªº¿z¿ï¸ê®Æ,¦ý§Ú­nÅý¿z¿ï¦Û°Ê¨ú®ø­n«ç»ò¼g?
   e)³Ì«á¤@­Ó°ÝÃD,¦p«e­±§A©Ò¼gªºµ{¦¡11¦æ     a.Copy .[A1],¬O±N«á­±ªºµ{¦¡¥s°µ[A1],­Y¦P¤@­Ó¥¨¶°¤¤§Ú·Q¥Î¦P¼Ëªº¤âªkcopy¤£¦Pªº¸ê®Æ,«h¬O§_­n±N[A1]§ï¬°[A2]©Î¨ä¥¦¥N½X?

TOP

¤j¤j,¤£¦n·N«ä¦A¸É­Ó°ÝÃD:
    ¥H«e­±ªºµ{¦¡ a.Copy .[A1],¬Ocopy¸ê®Æ(§t®æ¦¡,¤½¦¡),§Ú·Q°Ý¨ä¥Lªº¼gªk¦p¤U:
a) copy¸ê®Æ(§t®æ¦¡)¤§«á¶K¤W­È=Åý­ì¥ýªº¸ê®Æ¨S¦³¤½¦¡
b) copy¸ê®Æ¥u¶K¤W­È(¤£§t®æ¦¡)
½Ð½ç±Ð³o¤GºØ¼gªk.

TOP

¦^´_ 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.     .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 .Range("A1")  '§¹¥þ½Æ»s
  12.      A.Copy
  13.     .Range("A1").PasteSpecial xlPasteValues '¿ï¾Ü©Ê¶K¤W­È
  14.    
  15.     .Columns("BO:BQ").Delete Shift:=xlToLeft
  16.     .Columns("BD:BM").Delete Shift:=xlToLeft
  17.     .Columns("AX:AX").Delete Shift:=xlToLeft
  18.     .Columns("AM:AU").Delete Shift:=xlToLeft
  19.     .Columns("AA:AA").Delete Shift:=xlToLeft
  20.     .Columns("V:W").Delete Shift:=xlToLeft
  21.     .Columns("S:T").Delete Shift:=xlToLeft
  22.    End With
  23.    '.SaveAs "D:\¥t¦s·sÀÉ.xlsx"   '¦¹³BÀx¦sÀÉ®×
  24.    End With
  25.    .ShowAllData 'Åã¥Ü©Ò¦³¸ê®Æ
  26.    End With
  27. End Sub
½Æ»s¥N½X
¶K¤Wµ{¦¡½X­n¥[¤J¦æ¸¹¡A¦p¹Ï¾Þ§@
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ·R¤£¬O­n¨D¹ï¤è¡A¦Ó¬O­n¥Ñ¦Û¨­ªº¥I¥X¡C
ªð¦^¦Cªí ¤W¤@¥DÃD