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

[µo°Ý] ½Ö¯àÀ°³o´X¥G­«½Æ°Ê§@ªºVBA½G¨­ ÁÂÁÂ

[µo°Ý] ½Ö¯àÀ°³o´X¥G­«½Æ°Ê§@ªºVBA½G¨­ ÁÂÁÂ

¥»©«³Ì«á¥Ñ lcctno ©ó 2015-8-23 18:52 ½s¿è

¥Ñ©ó¦b¤U¬Ovbaªì¾ÇªÌ ¬G¹Á¸Õ±N§Ú¨Ï¥Î¨ç¼Æ¼gªº¼Æ¦Êmb¤§excellÀÉ ¨Ï¥Î¿ý»s¥¨¶°ªº¤è¦¡ ¨Ó±N¸ÓÀɽG¨­ ÁöµM½G¨­«á¥i¥¿±`¨Ï¥Î ¦ý§Ú·QÀ³¸Óµ{¦¡½X¤]¦³Â²¤Æªº¥i¯à©Ê
§Úª¾¹D¥i¥H±N  Rows("3:101").Select §ï¦¨ Rows("3:1001").Select ¦ý·|µo¥ÍÃþ¦ü¨t²Î¸ê·½¤£°÷ªºÄµ§i»y ¬G¥u¦n¼g¦¨¤@¦¸¥u§PÂ_100µ§ªº¸ê®Æ ¦ý§Ú¥Ø«eªº¸ê®Æ¤]±µªñ6000µ§¤F ¥BÁÙ·|Ä~Äò¼W¥[
§Æ±æ¦³°ª¤â¯àÀ°§U§Ú²¤Æ¥¦ ¨ÃÅý¥¦¯à¶¶§Qªº°õ¦æ¨ì 10001µ§ (¬°±N¨Ó¯d«á¸ô) ÁÂÁ¤F

¥Ø«e½G¨­ªº³B²z¹Lµ{¬O±N¨C¤@¤À­¶ «ü«O¯d¤@¦C¤§¨ç¼Æ ¨Ï¥Îvba±N¤§½Æ»s¨Ã¶K¤W99¦C¨ç¼Æ«á ¦b±Nªþ»s¤W¤§µ²ªGÅܦ¨¯Â¤å¦r¦A¶K¦^­ì³B ¨Ì¦¹Ãþ±À...¤u§@ªí.¥Ñ"ªR1" ¨ì"ªR10"  Á`¦@10¤À­¶ ¦pªþ¹Ï






¤À¹j½u++++++++++++++++++++++++++++++++++++++++++++++
'§PÂ_¦C1¤§­È¬O§_¦b¤u§@ªí"Data"¹ïÀ³¤§¨C¤é°ª§C­Èªº½d³ò¤º

Sub §PÂ_¦C()

'2~101

'»s§@§PÂ_¦C
    Sheets("ªR1").Select
    Rows("2:2").Select
    Selection.Copy
    Rows("3:101").Select
    ActiveSheet.Paste
   
'½G¨­
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
'»s§@§PÂ_¦C
    Sheets("ªR2").Select
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Rows("3:101").Select
    ActiveSheet.Paste
        
'½G¨­
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   
  ...............
. ....²¤¹L«Ü¦h¤ñ¤uµ{½X...............


'»s§@§PÂ_¦C
Sheets("ªR10").Select
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Rows("202:301").Select
    ActiveSheet.Paste
            
'½G¨­
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
Sheets("Data").Select
End Sub


¶×¥X¤§¥¨¶°
§PÂ_¦C.bas.zip (640 Bytes)

Sub ZZ()
Dim ar, br
ar = Array("ªR1", "ªR2", "ªR3", "ªR4", "ªR5", "ªR6", "ªR7", "ªR8", "ªR9", "ªR10")
For i = 0 To UBound(ar)
    Select Case i
    Case 0 To UBound(ar) - 1
        With Sheets(ar(i))
            .Rows(2).Copy .Rows("3:101")
            br = .Rows("3:4")
            .Rows("3:101") = br
        End With
    Case UBound(ar)
        With Sheets(ar(i))
            .Rows(2).Copy .Rows("201:301")
            br = .Rows("201:301")
            .Rows("201:301") = br
        End With
    End Select
Next
End Sub

TOP

¦^´_ 2# ikboy

·PÁ±zªº¼ö¤ßÀ°§U ¦ý³o¼Ë°õ¦æ¥u¯à¤ñ¹ï300µ§¸ê®Æ §Ú¬O·Q­n¯à°õ¦æ¨ì10001
ÁÙ¦³·Q­n½Ð±Ð ¥i§_¯à¥Ñ¥~³¡¤§­È ¨Ò¦p©ó "Data" ¤§¤À­¶¤º ¥[¤J2­Ó¿é¤J®æ ¥i¥H¿é¤J  [°_©l¦C¼Æ]="Data"¤§L4     [µ²§ô¦C¼Æ]="Data"¤§M4 °µ°Ï¬q¤§¤ñ¹ï ³o¼Ë°õ¦æ°_¨Ó·|¬Ù«Ü¦h®É¶¡
¦b¦¹¥ýÁÂÁ±zªº¶O¤ß¤F

TOP

¦^´_ 3# lcctno


Sub ZZ()
Dim ar, br, MyRng
Myrng = sheets("Data" ).[L4] & ¡§:¡¨ & sheets("Data" ).[M4]        Rem[°_©l¦C¼Æ]="Data"¤§L4     [µ²§ô¦C¼Æ]="Data"¤§M4      
ar = Array("ªR1", "ªR2", "ªR3", "ªR4", "ªR5", "ªR6", "ªR7", "ªR8", "ªR9", "ªR10")
For i = 0 To UBound(ar)
    Select Case i
    Case 0 To UBound(ar) - 1
        With Sheets(ar(i))
            .Rows(2).Copy .Rows(myrng)
            br = .Rows(myrng)
            .Rows(myrng) = br
        End With
    Case UBound(ar)
        With Sheets(ar(i))
            .Rows(2).Copy .Rows("201:301") Rem ¦¹³B¦p¦¹Ãþ±À
            br = .Rows("201:301")
            .Rows("201:301") = br
        End With
    End Select
Next
End Sub

TOP

¦^´_ 4# ikboy

°e¤W­ìÀÉ ½Ð±z¸Õ¸Õ ¤£ª¾¹D¨ºùØ¥X°ÝÃD ¨ä¤¤
[°õ¦æ]¤§«ö¶s¬O§Ú¿ý»sªº ¥i¥H¥¿±`°õ¦æ7000¦C¤§¸ê®Æ ¦ý°õ¦æ7000¦C­nªá¶O«Ü¤[ªº®É¶¡
[xx]¤§«ö¶s¬O±z²Ä¤@¦¸¼gªº °õ¦æ¿ù»~(«Dµ{¦¡½X¿ù»~)
[yy]¤§«ö¶s¬O±z²Ä¤G¦¸¼gªº °õ¦æ¿ù»~(«Dµ{¦¡½X¿ù»~)

Áٽбz¦h·ÓÅU¤F ÁÂÁ±z

   
~test% -.zip (396.17 KB)

TOP

¬Ý¨ì¤F°ÝÃD¬O¥N½X¤¤ªº¤¤¤å¦r·½©ó¤£¦P»y¨t, ¦b±zªºÀɮפ¤¶Ã½X¤F, ½Ð¤â°Ê§ó§ï
ar = Array("ªR1", "ªR2", "ªR3", "ªR4", "ªR5", "ªR6", "ªR7", "ªR8", "ªR9", "ªR10")

TOP

³z¹L¤u§@ºÞ²z­û¤§³B²zµ{§Ç¥iÆ[¹îEXCEL¨Ï¥Î¨t²Î¸ê·½¤§±¡ªp¡C
§ÚªºÀô¹Ò¡G°O¾ÐÅé4G,¥i¥Î2.9G¡AEXCEL¥Î¹F299,000K®É´N·|·í¡C
¥u¶}¡i~test% -.xls¡jÀÉ¡A¤£¶}¨ä¥LÀÉ¡A
(1)¥¼°õ¦æ«e¡AEXCEL¨Ï¥Îªº¨t²Î¸ê·½¡GCPU¨Ï¥Î²v26%¡A°O¾ÐÅé¥Î91,372K
(2)°õ¦æ«á¡A¤ÀªR¦Ü6®É¡AEXCEL¨Ï¥Îªº¨t²Î¸ê·½¡GCPU¨Ï¥Î²v61%¡A°O¾ÐÅé¥Î209,336K
(3)¤ÀªR¦Ü7®É¡AEXCEL¨Ï¥Îªº¨t²Î¸ê·½¡GCPU¨Ï¥Î²v55%¡A°O¾ÐÅé¥Î285,176K
(4)¤ÀªR¦Ü8®É¡AEXCEL¨Ï¥Îªº°O¾ÐÅé¹O290,000K®É¡A¥ÎESC¤¤Â_§@·~¡C
¤½¦¡¬Û·í¯Ó¸ê·½¡A³o¤äµ{¦¡¡A´X¥G©Ò¦³ªº¤u§@ªí³£¦³¤½¦¡¡A
¦pªG¤£¯à±j¤Æ¨t²Î¸ê·½¡A«Øij©îÀÉ¡A±N¤½¦¡Âনµ{¦¡©Mµ²ªG¸ê®Æ¤À¶}§e²{¡C
[color=blue]KY[/color]

TOP

¦^´_ 6# ikboy

©_©Ç  ¦b§Ú³oºÝ¬Ý°_¨Ó«Ü¥¿±` ¨Ã¨S¦³¶Ã½X???
¥B³£¯à°õ¦æ

TOP

¥»©«³Ì«á¥Ñ lcctno ©ó 2015-8-25 10:51 ½s¿è

¦^´_ 7# don4831
¥ý·PÁ±zªº¥Î¤ß»P«Øij ¨¯­W±z¤F
´N¬O¦]¬°³oÀÉ®×°õ¦æ°_¨Ó ¯uªº«Ü¦û¸ê·½ ¥B¤S¶O®É ¤w«j±j¨Ï¥Î¼Æ¦~¤F (¦]¬°§Ú¥u·|¥Î¨ç¼Æ¼g) ©Ò¥H¤~·|¶K¤å ·Q§ï½Ð°ª¤âÀ°§U§Ú §ï¥ÎVBA¤§¤uµ{¼gªk

TOP

³z¹L°_¦æ¤Î¨´¦æ°õ¦æ¡A¥i¶W¹L¸Uµ§copy¡G
Sub test()
    Rem '°_¦æ=3¡A¨´¦æ=10000¡A°õ¦æ®É¶¡¡G16:13:30~16:47:10
    Application.ScreenUpdating = False
    lineBegin = Worksheets("Data").Range("I4") '°_¦æ
    lineEnd = Worksheets("Data").Range("J4") '¨´¦æ
    For i = 1 To 10
        Worksheets("ªR" & i).Activate
        Rows(2).Copy
        For j = lineBegin To lineEnd
            Rows(j).PasteSpecial Paste:=xlPasteValues
        Next
        Rem ¨ú®ø½Æ»s¼Ò¦¡
        Application.CutCopyMode = False
    Next
    Application.ScreenUpdating = True
End Sub
[color=blue]KY[/color]

TOP

        ÀR«ä¦Û¦b : ¥Í®ð¡A´N¬O®³§O¤Hªº¹L¿ù¨ÓÃg»@¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD