[µo°Ý] copy¸ê®Æ

PJChen

Dear,
³o­Óµ{¦¡²Ä¤@­Ó³¡¥÷:copy ´Áªì®w¦s ¤@ª½µLªk¶K¤W,½ÐÀ°¦£¬Ý¤U...
1)copy ´Áªì®w¦s ¦ý¤@ª½µLªk¶K¤W­È
¨Ó·½ÀÉ  ®w¦sªí
xRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set Rng = Range("S5:S" & xRow)
¶K¨ì¥ØªºÀÉ ³Ì·s®w¦s-¨Î«~
Workbooks("³Ì·s®w¦s-¨Î«~.xlsx").Sheets("¨Î«~").Activate
.Range("AM6:AM" & xRow).PasteSpecial xlPasteValues

2)copy ½LÂI¸ê®Æ °õ¦æ¨S°ÝÃD,¦ý¤w¸g¼g¤FApplication.ScreenUpdating = False
¬°¤°»ò°õ¦æ®É,¿Ã¹õ·|¤@ª½°{?

3) ¥t¦³¤@­Óµ{¦¡Sub ¥X³f(),¬O¦b¨ä¥Lµ{¦¡¤¤©I¥s,¦ý¥u»Ý¦b¨C¶g¤@¡B¥|°õ¦æ,½Ð°Ý­n¦p¦ó¼g³o¬qµ{¦¡Call Module211.¥X³f
Copy¸ê®Æ.rar (241.95 KB)

¤U¸ü¦¸¼Æ: 10

2020-3-1 17:40

jcchiang

¦^´_ 1# PJChen

¸Õ¸Õ¬Ý
±NWorkbooks("³Ì·s®w¦s-¨Î«~.xlsx").Sheets("¨Î«~").Activate
.Range("AM6:AM" & xRow).PasteSpecial xlPasteValues
§ï¬°-->
Workbooks.Open(Path & "³Ì·s®w¦s-¨Î«~.xlsx").Sheets("¨Î«~").Activate
Range("AM6:AM" & (xRow + 1)).PasteSpecial xlPasteValues '¿ï¾Ü©Ê¶K¤W­È

PJChen

¦^´_ 2# jcchiang
Dear jcchiang,

¦³³]¸ô®|ªº"®w¦sªí"¬O¨Ó·½ÀÉ
xRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set Rng = Range("S5:S" & xRow)
"S5:S"¦³¦X¨ÖÀx¦s®æ, ¤@ª½µLªk¶K¨ì¥ØªºÀÉ ³Ì·s®w¦s-¨Î«~

Workbooks("³Ì·s®w¦s-¨Î«~.xlsx").Sheets("¨Î«~")¦]¬°¤u§@®É,¤@ª½³£¬O¶}±Òªº,©Ò¥H¨S¦³³]¸m¸ô®|,©Ò¥H¤£»Ý­nOpen(Path & ...
§ï¬°¥H¤UÁÙ¬OµLªk¶K...
Workbooks.Open(Path & "³Ì·s®w¦s-¨Î«~.xlsx").Sheets("¨Î«~").Activate
Range("AM6:AM" & (xRow + 1)).PasteSpecial xlPasteValues '¿ï¾Ü©Ê¶K¤W­È

¨ä¹ê§Ú¦b¨Ï¥Îcopyªº¥\¯à,¤@¯ë³£¥u¶K¤W­È,©Ò¥H§Ú³Ì·Q­nªº¼gªk¬OxRng = Rng.Values
¤£¹L´N¬O¤£¯à¶K¤W,»ª¦ü¤£¯à³o»ò¼g,§Ú·Qª¾¹DÁÙ¦³¤°»ò²¼äªº¼gªk¶Ü?
¥Ø«e§Ú¥ý¼È¥Î¼g¦ºªº¤èªk,¤£µMµLªk¹B§@,¦ý¤S¤£³ßÅw³o­Ó¦º¤èªk,Àx¦s®æ¤@¦ýÅÜ°Ê,«D±o­n­×§ï,¤£µM¸ê®Æ´N¿ù¤F¡I
Workbooks("³Ì·s®w¦s-¨Î«~.xlsx").Sheets("¨Î«~").Range("AM6:AM20") = Rng.Value

With Workbooks.Open(Path & File)
           Set W = Workbooks.Open(Path & File)
               With W.Sheets(i)
                   W.Sheets(i).Activate
xRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set Rng = Range("S5:S" & xRow)
              End With
Workbooks("³Ì·s®w¦s-¨Î«~.xlsx").Sheets("¨Î«~").Activate
Set xRng = Range("AM6:AM" & xRow)
xRng = Rng.Values

jcchiang

¦^´_ 3# PJChen

1.¦]¬°¤£¾å±oWorkbooks("³Ì·s®w¦s-¨Î«~.xlsx").Sheets("¨Î«~")¬O¤w¸g¶}±Ò,©Ò¥H¤~·|¦bµ{¦¡¤¤¼W¥[¶}±Ò
2.½d¨ÒÀɤ¤Range("S5:S" & xRow)¤¤xRow¬O19,¦ýWorkbooks("³Ì·s®w¦s-¨Î«~.xlsx").Sheets("¨Î«~")¤¤Range("AM6:AM" & xRow)¬OAM6:AM19©Ò¥H¶K¤£¤W
©Ò¥HRange("AM6:AM" & (xRow + 1)).¤~¦h¥[¤@¥hº¡¨¬¸ê®ÆÄæ¦ì
¦]¬°Range("S5:S19")¸òRange("AM6:AM19")®t¤@¦C

PJChen

¦^´_ 4# jcchiang

·PÁ±z¸Ô²Óªº¸Ñ»¡,¸g¹L¤ÏÂнm²ß,¨S°ÝÃD¤F,«D±`ÁÂÁÂ...

PJChen

½Ð°Ý  µ{¦¡Sub ¥X³f(),¬O¦b¨ä¥Lµ{¦¡¤¤©I¥s,¦ý¥u»Ý¦b¨C¶g¤@¡B¥|°õ¦æ,¤£¬O³o­Ó®É¶¡,«h¤£¥Î©I¥sModule211.¥X³f,§Ú¸ÕµÛ¼g¦¨¥H¤U,¦ý¤@ª½µLªk°õ¦æ¡I
        If Weekday(Day, 2) Or Weekday(Day, 5) Then
                Call Module211.¥X³f
            Else
            Exit For ' ¤¤Â_
        End If

­ã´£³¡ªL

¦^´_ 6# PJChen

©P¤@&©P¥|
If Weekday(Day, 2)= 1 Or Weekday(Day, 2) = 4 Then

PJChen

¦^´_ 7# ­ã´£³¡ªL

­ã¤j¦n,
¤µ¤Ñ¬O¶g¥|,¥¿¦n¥i¥H´ú¸Õµ{¦¡,µ²ªG§Ú³æ¿W°õ¦æ Module211.¥X³f ®É¨S°ÝÃD,¦ý¥H¤Uµ{¦¡,¬O§_­þ­Ó»yªkÁÙ¦³¿ù»~?ÁÙ¬O¤£¯à°õ¦æ吔...
        If Weekday(Day, 2) = 1 Or Weekday(Day, 2) = 4 Then
                Call Module211.¥X³f
            Else
            Exit For
        End If

jcchiang

¦^´_ 8# PJChen

If Weekday(Date, 2) = 1 Or Weekday(Date, 2) = 4 Then
                 Call Module211.¥X³f
            Else
             Exit For
         End If