[µo°Ý] ¸ê®Æªº½Æ»s¡B¶K¤W¡A»yªk°ÝÃD

iceandy6150

¦U¦ì¦n  ·Q½Ð°Ý¤@¨Ç»yªk¤Wªº¿ù»~

¤@¡B¦P¤@­ÓÀɮסA¤£¦Psheet¤§¶¡ªº½Æ»s ¶K¤W
With Sheets("¤u§@ªí1")
a = .UsedRange.Rows.com

1. Sheets("¤u§@ªí1").Range("A1:B" & a).Copy
   
2. 
   
3. Sheets("µ²ªG°Ï").Range("A1").Select
   
4. 
   
5. Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
   
6. , SkipBlanks:=False, Transpose:=False
   
7. 
   
8. Application.CutCopyMode = False

½Æ»s¥N½X

End with

§Ú·Q§âSheet¤u§@ªí1ªº  ¤w¨Ï¥Î¦C¼Æ ªº¸ê®Æ¡A½Æ»s¶K¤W¨ì Sheetµ²ªG°Ïªº A1
¥i¬O¦b³o¤@¦æ¸ò§Ú»¡¥X¿ù  Sheets("µ²ªG°Ï").Range("A1").Select
¬°¤°»ò©O?


¤G¡B¤£¦PÀɮפ§¶¡ªº½Æ»s¡B¶K¤W

°²¨Ï¦³¨â­ÓÀÉ®×   ¤@­Ó¥s  ¸ê®Æ.xlsx   ¤@­Ó¥s   ÀÉ®×.xlsx
§Ú²{¦b¬O¥Î
Windows("¸ê®Æ.xlsx").Activate
.Range("A1:B3").copy

Windows("ÀÉ®×.xlsx").Activate
.Range("A1").select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False

´N¬O­n¥Î½Ö¡A½Ö´N¥ýActive
©Î¬O­n¼g«Üªø
e=Workbooks("¸ê®Æ.xlsx").Sheets("¤u§@ªí1").Range("A1:B3").Value
Workbooks("ÀÉ®×.xlsx").Sheets("¤u§@ªí1").Range("A1:B3").Value = e

¦pªGª½±µ±µ¦b¤@°_¦n¹³¤]·|¿ù
¨Ò¦p¡G
Workbooks("ÀÉ®×.xlsx").Sheets("¤u§@ªí1").Range("A1:B3").Value = Workbooks("¸ê®Æ.xlsx").Sheets("¤u§@ªí1").Range("A1:B3").Value

¦]¬°§Ú¬O·s¤â
¦³®É­Ô¹ï©ó³o­Ó´N«Ü§xÂZ
©ú©ú§Ú¤w¸g§i¶D¹q¸£  ­þ­ÓÀɮתº­þ­Ósheetsªº­þ­ÓRange¤F
§Ú´N¥u¬O·Q­nBÀɮתº³o­Ó¦ì¸m = AÀɮתº¬Y­Ó¦ì¸mªº­È ¦Ó¤w
¦ý¬O±`±`¥X¿ù

©Î¬O´N­n¥ý¥Î¤@­Ó e = ¬Y¬Y¬Y   µM«á°é°é°é¦A = e

·d±o§Ú«Ü²V¶Ã
¦A³Â·Ð¦U¦ì¤j¤j¸Ñµª
ÁÂÁÂ

n7822123

¦^´_ 1# iceandy6150

¥i¬O¦b³o¤@¦æ¸ò§Ú»¡¥X¿ù  Sheets("µ²ªG°Ï").Range("A1").Select
¬°¤°»ò©O?

a = .UsedRange.Rows.com
À³¸Ó¬O
a = .UsedRange.Rows.count


¤G¡B¤£¦PÀɮפ§¶¡ªº½Æ»s¡B¶K¤W

§AªºÃD¥Ø¹ê¦b¤Ó¼s¤F.......¼gªk¤d¦ÊºØ
¥u¯à»¡·s¤â¥u¯à¦h¬Ý§O¤H«ç»ò¼gªº
³Ì«á¤~¯à§ä¨ì¦Û¤v²ßºDªº¼gªk

­ìµ{¦¡:
Windows("¸ê®Æ.xlsx").Activate
.Range("A1:B3").copy

Windows("ÀÉ®×.xlsx").Activate
.Range("A1").select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False

(1)¥i¥H³]ª«¥ó¨Ó²µuªø«× ¦p¤U:

Set ¸ê®Æªí=workbooks("¸ê®Æ.xlsx").sheets("¤u§@ªí1")
Set Àɮתí=workbooks("ÀÉ®×.xlsx").sheets("¤u§@ªí1")
¸ê®Æªí.[A1:B3].copy
Àɮתí.[A1].PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False


(2)¦pªG±Ò¥Îªº¤u§@ªí¦b"¸ê®Æ.xlsx"ªº"¤u§@ªí1"¡A
     °ò¥»¤W¥H«á¥Î³o­Ó¤u§@ªí¡A«e­±ªº"¦a§}"´N¥i¥H¬Ù²¤¤F
     ¥ÎActivate+With...End With ¨Ó²µuªø«× ¦p¤U

workbooks("¸ê®Æ.xlsx").sheets("¤u§@ªí1").Activate

with workbooks("ÀÉ®×.xlsx").sheets("¤u§@ªí1")
    [A1:B3].copy  
    .[A1].PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
    , SkipBlanks:=False, Transpose:=False  
end with


[A1:B3].copy  ³o¸Ì¬Ocopy "¸ê®Æ.xlsx"ªº"¤u§@ªí1"ªº[A1:B3]¡A¦]¬°¬O±Ò¥Îªº¤u§@ªí¡A«e­±ª½±µ¬Ù²¤!
ª`·N[A1]«e­±¦h¤F.¡A©Ò¥H¬O¦b"ÀÉ®×.xlsx"ªº"¤u§@ªí1"ªº[A1]¶K¤W

iceandy6150

¦^´_ 2# n7822123


    ·PÁ¤j¤jÁ¿¸Ñ¡A­ì¨Ó³W«h¬O³o¼Ë

¦^¨ì§Ú²Ä¤@ÃD

1. With Sheets("¤u§@ªí1")
   
2. 
   
3. a = .UsedRange.Rows.Count
   
4. Sheets("µ²ªG°Ï").Range("A1:Z200").Delete Shift:=xlShiftUp    '²M°£©Ò¦³¸ê®Æ
   
5. Sheets("¤u§@ªí1").Range("A1:B" & a).Copy
   
6. Sheets("µ²ªG°Ï").Range("A1").Select
   
7. Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
   
8. , SkipBlanks:=False, Transpose:=False
   
9. 
   
10. Application.CutCopyMode = False
    
11. End with

½Æ»s¥N½X

»¡¿ù¦b¨º¤@¦æ

¤U¸ü (10.03 KB)

2019-6-20 01:52

¬O¦]¬°¥Î¤F With Sheets("¤u§@ªí1")
©Ò¥H§ÚSheets("µ²ªG°Ï").Range("A1").Select ³o¼Ë´N·|¿ù¶Ü?
ÁÙ¬O¥t¦³­ì¦]
ÁÂÁÂ

n7822123

¦^´_ 3# iceandy6150


¦b«D±Ò¥Îªº¤u§@ªí°µ¿ï¨ú¡A·|µo¥Í¿ù»~

©Ò¥HºÉ¶q¤£­n¥Î Select¡A¤U­±µ{¦¡¦P²z¡A¸Õ¸Õ¬Ý

Sheets("¤u§@ªí1").Activate
Sheets("µ²ªG°Ï").Range("A1").Select

¿ï¨ú¤§«e¡A­n¥ý§ï±Ò¥Îªº¤u§@ªí¡A
§ï¦¨¤U­±³o¼Ë´N¥i¥H¤F

Sheets("¤u§@ªí1").Activate
Sheets("µ²ªG°Ï").Activate
Sheets("µ²ªG°Ï").Range("A1").Select

n7822123

¦^´_ 4# n7822123

¥ç©ÎªÌ¥ÎApplication.Goto¨ú¥NSelect

Sheets("¤u§@ªí1").Activate
Application.Goto Sheets("µ²ªG°Ï").Range("A1")

iceandy6150

¦^´_ 5# n7822123


    ·PÁ±zªº±Ð¾Ç

¦pªG¬O¥H³oºØ¼gªk
************************************
¿ï¨ú¤§«e¡A­n¥ý§ï±Ò¥Îªº¤u§@ªí¡A
§ï¦¨¤U­±³o¼Ë´N¥i¥H¤F

Sheets("¤u§@ªí1").Activate
Sheets("µ²ªG°Ï").Activate
Sheets("µ²ªG°Ï").Range("A1").Select
************************************

¦pªG¦³«Ü¦h¸ê®Æ­n§PÂ_¡A´N·|¨â­ÓÀɮפ@ª½Active
²Ä¤@ÀÉActive¡A½Æ»s¡A²Ä¤GÀÉActive¡A¿ï¨ú¡A¶K¤W (³o¬O§PÂ_¤@µ§¦Ó¤w)
¦pªG¦³100µ§­n§PÂ_ªº¸Ü¡A²Å¦X±ø¥ó´N½Æ»s¹L¥h¡A¤£²Å¦X´N¤£°Ê§@
´N·|¤@ª½Active
µe­±´N¸õ°Ú¸õ°Ú¡A¨þ¨þ
¤£¹L¯à¦¨¥\§¹¦¨­n°µªº¨Æ´N¦n

GOTO¥i¯à´N¤ñ¸û¤£·|¸õ¨Ó¸õ¥h

¦A¦¸·PÁÂ

n7822123

¦^´_ 6# iceandy6150

©Ò¥H¤£­n¥Îselect,¥i°Ñ¦Ò§Ú2¼Óªº²Ä2­Ó¼gªk
©T©w¤@­Ó¤u§@ªíactivate,¨ä¥Lªº¥Îwith¡K¡Kend with
for¦^°é¦bwith¤§¤º §A­n³B²z1000­Ó¤]¨S°ÝÃD

­ã´£³¡ªL

¦^´_ 3# iceandy6150

With Sheets("¤u§@ªí1")
       a = .UsedRange.Rows.Count
       Sheets("µ²ªG°Ï").Range("A1:Z200").Delete Shift:=xlShiftUp    '²M°£©Ò¦³¸ê®Æ
           .Range("A1:B" & a).Copy
       Sheets("µ²ªG°Ï").Range("A1").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
      , SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End with

iceandy6150

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


    §Ú¤µ¤Ñ¹J¨ì¤@­Ó·s°ÝÃD
¤@­ÓÀɮסA¨â­Ósheet¡G 1.¤u§@ªí1    2. PPT
§Ú­n§â¸ê®Æ±q¡i¤u§@ªí1¡j¡A½Æ»s¨ì¡iPPT¡j

¦ý¬Oµ{¦¡µ²ªG¦³»~¡A¥i¯à¤S¬O§Ú»yªk¦³°ÝÃD¤F

1. Private Sub CommandButton6_Click()
   
2. 
   
3. If Sheets("PPT").Range("A1").Value <> "" Then
   
4.   Sheets("PPT").Range("A1:Z200").Delete Shift:=xlShiftUp    '²M°£©Ò¦³¸ê®Æ
   
5. End If
   
6. 
   
7. Sheets("¤u§@ªí1").Range("A1").CurrentRegion.Copy
   
8. 
   
9. Sheets("PPT").Activate
   
10. 
    
11. ActiveSheet.[A1].Select
    
12. Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
    
13. , SkipBlanks:=False, Transpose:=False
    
14. 
    
15. Application.CutCopyMode = False
    
16. 
    
17. a = UsedRange.Rows.Count
    
18. 
    
19. For i = 2 To a
    
20.   Range("U" & i).Value = Range("G" & i).Value + 1
    
21. Next i
    
22. End sub

½Æ»s¥N½X

§Ú¤¤¶¡¤w¸g§â¡iPPT¡jActivate¤F
a¥h­pºâ¤w¸g¨Ï¥Î¦C¼Æ¡A¬O­pºâ¡iPPT¡jªº¶Ü   ÁÙ¬O­pºâ ¡i¤u§@ªí1 ¡jªº?
¦]¬°¤U­±°µ°j°éªº®É­Ô¡Aµ²ªG©~µM¬O©ñ¦b ¡i¤u§@ªí1¡j ªºUÄæ¡A¦Ó¤£¬O©ñ¦b¡iPPT¡jªºUÄæ
Range("U" & i).Value = Range("G" & i).Value + 1  ³o¦æ ¦pªG¼g .Range ¤S¸ò§Úĵ§i¿ù»~

«á¨Ó¥u¦n§ï¦¨
For i = 2 To a
  Sheets("PPT").Range("U" & i).Value = Sheets("PPT").Range("G" & i).Value + 1
Next i
³o¼Ë¤~¯à©ñ¦bPPTªºUÄ椤


Sheets("¤u§@ªí1").Range("A1").CurrentRegion.Copy
³o¤@¦æ§Ú¥»¨Ó¬O¼g[A1].CurrentRegion.Copy
¦ý¬O§Úµo²{¡A¦pªG§Ú§âµe­±ÂI¦b¡iPPT¡j¡A¦A«öF8³v¨B´ú¸Õµ{¦¡
µ²ªG´N·|¥h½Æ»s¡iPPT¡jªºA1¡A¦Ó¤£·|¥h½Æ»s¡i¤u§@ªí1¡jªºA1

³o­Ó"¦a§}"¸Ó¬Ù¤£¸Ó¬Ù¡AÁÙ¦³"¥Ø«e±Ò¥Î"ªº¹ï¶H¬O½Ö
§ÚÁÙ¬O¦³ÂI¼Ò½k
¦A½Ð¦U¦ì¤j¤j¸Ñµª¤F
ÁÂÁÂ
µù¡G³o­Ó«ö¶s¬O¼g¦b ¤u§@ªí1

­ã´£³¡ªL

¦^´_ 9# iceandy6150


¬°«OµL»~,
RANGE©ÎUSEDRANGE«e­±³Ì¦n«a¤WSHEETS("???")