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

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

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

³Ìªñ¤@ª½¦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

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

TOP

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?

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-1-21 11:49 ½s¿è

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

TOP

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

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-1-21 13:56 ½s¿è

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

TOP

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

TOP

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

TOP

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

TOP

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

TOP

        ÀR«ä¦Û¦b : ¤H­n¦Û·R¡A¤~¯à·R´¶¤Ñ¤Uªº¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD