[µo°Ý] ²¤Æ¥¨¶°µ{¦¡

PJChen

³Ìªñ¤@ª½¦b­×§ï¥¨¶°µ{¦¡,¦]¬°À´±o¤£¦h,©Ò¥H½Ñ¦h°ÝÃD,½Ð¨£½Ì.
³Â»¶®a±Ú¦U¦ì¼ö¤ß¤H¤hÀ°¦£§Ú¼g¤F¨Çµ{¦¡,§Ú¦Û¤vµy¥[­×§ï¦¨©Ò»Ý­nªº¥¨¶°µ{¦¡,¥H¤U³o­ÓÁöµM§ï¤F¦n´X­Ó¤p®É,¥i¥H¥Î¤F,¦ý§Úª¾¹D¦³«Ü¦h­«½Æªº»y¨¥,¨Ï±o¥¨¶°¶]±o«ÜºC,§Ú¤w¸g¸ÕµÛ­×§ïN¦¸¤F,³£¦æ¤£³q,¤£ª¾¦³¨S¦³¤H¥i¥HÀ°§Úºë²¥¦?
Sub copy_from_Multi_format()
'
' copy_from_Multi_format ¥¨¶°

   With Workbooks("2011 BCMart Multi-Format.xlsx").Sheets("BCM±±ºÞ")
    .Columns("A:CZ").Hidden = False
    Set A = Intersect(.UsedRange, .Range("A:CZ")).SpecialCells(xlCellTypeVisible)
    With Workbooks("VBA Cluster.xlsm")  '½Ð§ï¦¨­n½Æ»s¬¡­¶Ã¯ªº¦WºÙ(¤w¸g¥´¶})
    With Sheets("BCM±±ºÞ")
    .Columns("A:CZ").Hidden = False
    A.Copy Workbooks("VBA Cluster.xlsm").Sheets("BCM±±ºÞ").Range("A1")  '§¹¥þ½Æ»s
   
   End With
   End With
   End With
   
       With Workbooks("VBA Cluster.xlsm")  '½Ð§ï¦¨­n½Æ»s¬¡­¶Ã¯ªº¦WºÙ(¤w¸g¥´¶})
    With Sheets("BCM±±ºÞ")
    Set A = Intersect(.UsedRange, .Range("A:CZ")).SpecialCells(xlCellTypeVisible)
    A.Copy
    .Range("A1").PasteSpecial xlPasteValues '¿ï¾Ü©Ê¶K¤W­È
   End With
   End With
   
    Workbooks("VBA Cluster.xlsm").Activate
    Sheets("BCM±±ºÞ").Select
    Columns("F:F").Select
    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Workbooks("VBA Cluster.xlsm").Activate
    Sheets("VBA").Select
    Range("B1").Select
End Sub

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

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

¦^´_ 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¤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

¦^´_ 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

¤U¸ü (224.12 KB)

2012-1-21 14:12

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

GBKEE

¦^´_ 7# PJChen
§A±N½Æ»sµ{¦¡½X«á,¶K¦b¤w¦³ªºµ{¦¡½X¤U¤è·íµM·|¦b¨º¸Ì¥X²{.
§R±¼  ©Î²¾ ¨ì³»ºÝ  ´N¦n¤F.

PJChen

¤]¥i¥H§R°£ªº¶Ü?ÁÂÁÂ.

GBKEE

¦^´_ 9# PJChen
¦p¦³ ³o¨ÇOption  ³£¥²¶·¸m©ó ¼Ò²Õªº³»ºÝ
Option Explicit     ³¯­z¦¡ : ¦b¼Ò²Õ¼h¦¸¤¤±j­¢¨C­Ó¦b¼Ò²ÕùتºÅܼƳ£¥²¶·©ú½Tªº«Å§i¡C
Option Compare ³¯­z¦¡ : ¦b¼Ò²Õ¼h¦¸¤¤¥Î¨Ó«Å§i·í¤ñ¸û¦r¦ê¸ê®Æ®É¡A©Ò¹w³]¨Ï¥Îªº¤ñ¸û¤èªk¡C
Option Base         ³¯­z¦¡ : ¦b¼Ò²Õ¼h¦¸¤¤¥Î¨Ó«Å§i°}¦C¯Á¤Þªº¹w³]¤U­­¡C
Option Private     ³¯­z¦¡ : ·í¥DÀ³¥Îµ{¦¡¤¹³\¥i¤Þ¥Î¸ó¶V¦h­Ó±M®×®É¡AOption Private Module ¥i¥HÁקK¼Ò²Õ³Q¥~³¡±M®×©Ò¤Þ¥Î¡C¦ý­Y¥DÀ³¥Îµ{¦¡¤£¤¹³\¸ó±M®×¤Þ¥Î®É¡A«h Option Private ¨S¦³§@¥Î¡C