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

PJChen

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¦³¸ê®Æªº°Ï¶ô¶Ü?

PJChen

ª©¤j,§Ú§ä¨ì­ì¦]¤F,³o­Óªí¤w¸gok. ÁÂÁ§A.
    With Workbooks("Chart-F.xlsx").Sheets("¤u§@ªí1")
    Set B = Intersect(.UsedRange, .Range("A:D")).SpecialCells(xlCellTypeVisible)
    Workbooks("2011 BCMart Multi-Format.xlsx").Activate
    Sheets("Factory's order").Select
    Range("A1").Select
    B.Copy
    Selection.PasteSpecial xlPasteValues   '¿ï¾Ü©Ê¶K¤W­È
    End With

PJChen

ª©¤j,
­×§ï«á¥i¥H°õ¦æ,¦ý°õ¦æ¤£§¹¥þ,¸Õ¤F¦n´X¦¸,µ²ªG³£¬Û¦P,¦ü¥G¥u¦³copy ÄæA¸ê®Æ,B:C¸ê®ÆCOPY¤£§¹¥þ,¥i§_À°§Ú¬Ý¬Ý¬°¤°»ò?TKS. Chart-F.zip (63.46 KB)

¤U¸ü¦¸¼Æ: 1

2012-1-20 23:38

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

PJChen

Dear,
§Ú·Q­n½Æ»s¸ê®Æ®É,§¹¥þ½Æ»s«á¶K¤W­È,¥i¬O°õ¦æ¨ì¿ï¾Ü©Ê¶K¤W­È´N¥¢±Ñ¤F,½Ð°Ý­þ¸Ì¥X¿ù¤F? Chart-F.zip (37.44 KB)

¤U¸ü¦¸¼Æ: 2

2012-1-20 21:57

   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
    B.Copy Workbooks("2011 BCMart Multi-Format.xlsx").Sheets("Factory's order").Range("A1").PasteSpecial xlPasteValues  '¿ï¾Ü©Ê¶K¤W­È,¥¢±Ñ¤F

    End With

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

PJChen

§Ú¸ÕµÛ­×§ïª©¥DÀ°§Ú¼gªºµ{¦¡,²{¦b¦³°ÝÃDªº¬O«á¥b¬q,copy "Chart-F",A:D¦³¼Æ­Èªº°Ï°ì,¶K¨ì¤w¶}±ÒªºÀÉ®×"2011 BCMart Multi-Format.xlsx" ªºsheets("Factory's order")ªºA1Àx¦s®æ,¦ý³£¶K¤£¤W,§Ú±NÀɮפW¶Ç,½Ð¤j®aÀ°§Ú¬Ý¬Ý°ÝÃD¦b­þ¸Ì,ÁÂÁÂ! Chart-F.zip (32.73 KB)

¤U¸ü¦¸¼Æ: 2

2012-1-20 12:34

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

PJChen

¶W¯Åª©¥D,ÁÂÁ±z¦^ÂÐ,«e­±©Ò¦^Âаõ¦æ§¹¥þOK.
§Ú±N±z¼gªºµ{¦¡­×§ï¨ì¥t¤@­ÓÀɮפ¤,¥H¤U¬O±N¸ê®Æ¥t¶}¤@­Ó·sªº¤u§@ªí(book1)¶K¤W,°õ¦æ¸Ó§R°£ªº¸ê®Æ«á,¥H¤U¬O§Ú·Q·s¼Wªºµ{¦¡:
§Ú·Q±N¾ã²z«áªº¸ê®Æ³Ñ¤UA:D,¥´¶}ÂÂÀɶK¤W¥u¦³A:D¦³¼Æ­Èªº³¡¥÷,¨ä¸ô®|¤ÎÀɦW¬°:P:\BCM\2011 BCMart Multi-Format.xlsx,¥t¤@­ÓÃøÃD¬O¦]¬°§Ú¥i¯à¥´¶}ªº¬Obook2 or book3¥h°õ¦æ¥H¤Uªºµ{¦¡,©Ò¥H­n¦p¦ó¼g¤~¯àÅý¥¦¨Ìbookxx¥hcopy¸ê®Æ?
Sub copy_Chart_F()
'
' copy_Chart_F ¥¨¶°
'

'
    Windows("VBA Cluster.xlsm").Activate
    With ThisWorkbook.Sheets("Chart-F")
    .Columns("D:AP").Hidden = False
    Set A = Intersect(.UsedRange, .Range("D:Z")).SpecialCells(xlCellTypeVisible)
    With Workbooks.Add
   With .Sheets(1)
   A.Copy .Range("A1")  '§¹¥þ½Æ»s
     A.Copy
    .Range("A1").PasteSpecial xlPasteValues '¿ï¾Ü©Ê¶K¤W­È
    .Columns("G:U").Delete Shift:=xlToLeft
    Columns("B:E").Delete Shift:=xlToLeft
    Columns("B:B").Select
    Selection.Cut
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
   
   End With
   
   End With
   End With
   
   
End Sub

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

PJChen

¤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.