PJChen

¤U¸ü (224.12 KB)

2012-1-21 14:12

GBKEE,ÁÂÁ§A,¤@¤Á¹B§@¥¿±`.
·Q½Ð±Ð:§Ú±Nµ{¦¡COPY¨ì¤u§@ªíªº®É­Ô,"Option Explicit"¬°¤°»ò³£·|¶]¨ì½uªº¤W¤è?

GBKEE

¦^´_ 5# PJChen

1. Option Explicit
   
2. Sub Acopy_from_Multi_format()
   
3.     Dim Wb(1 To 2) As Workbook, xS As Integer, Ar1(), Ar2()
   
4.     '*** «ü©wµ¹ÅܼÆ: ¦pµ{§Ç«Ü¤j ­×§ï¦WºÙ®É¤£¥²¦bµ{§Ç¤¤¤@¤@§ó§ï ***
   
5.     Dim Ar(1 To 2)
   
6.     Set Wb(1) = Workbooks("2011 BCMart Multi-Format.xlsx")
   
7.     Set Wb(2) = Workbooks("VBA Cluster.xlsm")
   
8.     Ar1 = Array("BCM±±ºÞ", "Factory xsip", "Chart")    '¸m¤J°}¦C
   
9.     Ar2 = Array("A:CZ", "A:AI", "A:AP")                '¸m¤J°}¦C
   
10.     For xS = 0 To UBound(Ar1)     '-> Array("BCM±±ºÞ", "Factory xsip", "Chart")
    
11.         With Wb(1).Sheets(Ar1(xS))                                  '¥N¤J Ar1(xS)
    
12.             .Columns("A:CZ").Hidden = False
    
13.             Intersect(.UsedRange, .Range(Ar2(xS))).SpecialCells(xlCellTypeVisible).Copy
    
14.                                    ''¥N¤J Ar2(xS) : ¥ý°µ½Æ»sªº°Ê§@
    
15.                 With Wb(2).Sheets(Ar1(xS))                              '¥N¤J Ar1(xS)
    
16.                     .Range("A1").PasteSpecial Paste:=xlPasteAll         '§¹¥þ½Æ»s
    
17.                     .Range("A1").PasteSpecial Paste:=xlPasteValues      '½Æ»s­È
    
18.                     .Columns("A:CZ").Hidden = False
    
19.             End With
    
20.         End With
    
21.     Next
    
22.     Application.CutCopyMode = False
    
23.     'CutCopyMode ÄÝ©Ê: ³]©w [°Å¤U] ©Î [½Æ»s] ¼Ò¦¡ªºª¬ºA¡C¥i¬° True¡BFalse ©Î¤@­Ó XLCutCopyMode ±`¼Æ¡A¦p¤U¦Cªí®æ©Ò¥Ü¡CŪ/¼g Long¡C
    
24. End Sub

½Æ»s¥N½X

PJChen

GBKEE¤j¤j,
¤T­Ó¤£¦PªºSHEET,¬O±Ä¥Î¦P¼Ë¤èªk¥h½Æ»s¤Î§¹¥þ¶K¤W¤Î¶K¤W­È,¦ý¨C­ÓsheetªºÄæ¦ì³£¤£¦P,©Ò¥HµLªk¥H¤@­P©Ê¤è¥h§@,½Ð°Ý§Ú­n«ç»ò§ï?·PÁ§A!
Workbooks("2011 BCMart Multi-Format.xlsx").Sheets("BCM±±ºÞ") A:CZ
Workbooks("2011 BCMart Multi-Format.xlsx").Sheets("Factory Ship") A:AI
Workbooks("2011 BCMart Multi-Format.xlsx").Sheets("Chart") A:AP

GBKEE

¦^´_ 3# PJChen

1. Option Explicit
   
2. Sub Acopy_from_Multi_format()
   
3.     Dim Wb(1 To 2) As Workbook, Sh As Worksheet    '©Î¬O Sh As Variant
   
4.     '*** «ü©wµ¹ÅܼÆ: ¦pµ{§Ç«Ü¤j ­×§ï¦WºÙ®É¤£¥²¦bµ{§Ç¤¤¤@¤@§ó§ï ***
   
5.     Set Wb(1) = Workbooks("2011 BCMart Multi-Format.xlsx")
   
6.     Set Wb(2) = Workbooks("VBA Cluster.xlsm")
   
7.     For Each Sh In Wb(1).Sheets(Array("BCM±±ºÞ", "Factory Ship", "Chart"))   '
   
8.         'For Each Sh In Array("BCM±±ºÞ", "Factory Ship", "Chart")   'Sh As Variant ¦p¦¹§@
   
9.         With Sh
   
10.             'With Wb(1).Sheets(Sh)                                  'Sh As Variant ¦p¦¹§@
    
11.             .Columns("A:CZ").Hidden = False
    
12.              Intersect(.UsedRange, .Range("A:CZ")).SpecialCells(xlCellTypeVisible).Copy
    
13.                                                                     '¥ý°µ½Æ»sªº°Ê§@
    
14.             With Wb(2).Sheets(Sh.Name)
    
15.              '   With Wb(2).Sheets(Sh)                              'Sh As Variant ¦p¦¹§@
    
16.                 .Range("A1").PasteSpecial Paste:=xlPasteAll          '§¹¥þ½Æ»s
    
17.                 .Range("A1").PasteSpecial Paste:=xlPasteValues       '½Æ»s­È
    
18.                 .Columns("A:CZ").Hidden = False
    
19.             End With
    
20.         End With
    
21.     Next
    
22.     Application.CutCopyMode = False
    
23.     'CutCopyMode ÄÝ©Ê: ³]©w [°Å¤U] ©Î [½Æ»s] ¼Ò¦¡ªºª¬ºA¡C¥i¬° True¡BFalse ©Î¤@­Ó XLCutCopyMode ±`¼Æ¡A¦p¤U¦Cªí®æ©Ò¥Ü¡CŪ/¼g Long¡C
    
24. End Sub

½Æ»s¥N½X

PJChen

GBKEE,¤W­zµ{¦¡°õ¦æ«Ü§Ö,¦ý¦³¤G­Ó°ÝÃD,¦]¬°³o¸ò§Ú¿ý»s¥¨¶°ªºµ{¦¡½X¤£¦P,§Ú¤£·|§ï,½ÐÀ°§Ú­×¥¿¥H¤U:

1. ¥þ³¡ªºµ{¦¡¦b¦P¤@¤u§@ªí·|¥Î¦P¼Ë¤èªk±q¤T­ÓSheets½Æ»s¸ê®Æ,§Y:
Workbooks("2011 BCMart Multi-Format.xlsx").Sheets("BCM±±ºÞ") copy ¨ì Workbooks("VBA Cluster.xlsm").Sheets("BCM±±ºÞ")
Workbooks("2011 BCMart Multi-Format.xlsx").Sheets("Factory Ship") copy ¨ì Workbooks("VBA Cluster.xlsm").Sheets("Factory Ship")
Workbooks("2011 BCMart Multi-Format.xlsx").Sheets("Chart") copy ¨ì Workbooks("VBA Cluster.xlsm").Sheets("Chart")

2. ¦]¬°¤u§@ªí"2011 BCMart Multi-Format.xlsx"³£¦³¤½¦¡,§Æ±æ¶K¤W¥t¤@­Ó¤u§@ªí Workbooks("VBA Cluster.xlsm")«á°£¤F§¹¥þ½Æ»s®æ¦¡¥~,ÁÙ­n§ï¦¨"¶K¤W­È"¤£­n¤½¦¡.

³oºØ±¡§Î¤U§Ú§ï¤°»ò­×§ïµ{¦¡½X?

GBKEE

¦^´_ 1# PJChen

1. Option Explicit
   
2. Sub Acopy_from_Multi_format()
   
3.     Dim Sh(1 To 2) As Worksheet
   
4.     '*** ¤u§@ªí «ü©wµ¹ÅܼÆ: ¦pµ{§Ç«Ü¤j ­×§ï¤u§@ªí¦WºÙ®É¤£¥²¦bµ{§Ç¤¤¤@¤@§ó§ï ***   
   
5.     Set Sh(1) = Workbooks("2011 BCMart Multi-Format.xlsx").Sheets("BCM±±ºÞ")
   
6.     Set Sh(2) = Workbooks("VBA Cluster.xlsm").Sheets("BCM±±ºÞ")
   
7.        With Sh(1)
   
8.         .Columns("A:CZ").Hidden = False
   
9.         Set A = Intersect(.UsedRange, .Range("A:CZ")).SpecialCells(xlCellTypeVisible)
   
10.         Sh(2).Columns("A:CZ").Hidden = False
    
11.         A.Copy Sh(2).Range("A1")  '§¹¥þ½Æ»s
    
12.     End With
    
13.     With Sh(2)  '½Ð§ï¦¨­n½Æ»s¬¡­¶Ã¯ªº¦WºÙ(¤w¸g¥´¶})
    
14.         .Columns("A:CZ").Hidden = False
    
15.         Set A = Intersect(.UsedRange, .Range("A:CZ")).SpecialCells(xlCellTypeVisible)
    
16.         A.Copy .Range("A1")  '§¹¥þ½Æ»s
    
17.         .Columns("F:F").Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
    
18.         SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    
19.         ReplaceFormat:=False
    
20.     End With
    
21.     Workbooks("VBA Cluster.xlsm").Sheets("VBA").Activate
    
22.     Range("B1").Select
    
23. End Sub

½Æ»s¥N½X