[µ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¸Ñµª
ÁÂÁÂ

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¦³­ì¦]
ÁÂÁÂ

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ÁÂ

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

iceandy6150

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


    ¦nªº¡A«D±`·PÁÂ

¥u¬O¦]¬°¦³®É­Ô«Üªø¤@¦ê
§Ú³£·Q¬Ù¤@¤U¡A¨þ¨þ

iceandy6150

UsedRange¬O­pºâ¬Y­Ó¤u§@ªíªº¨Ï¥Î½d³ò~~~©Ò¥H«e­±³Ì¦n«ü©w¬O­þ­ÓSheet
§Aªº«ö¶s¦b¤u§@ªí1¡A©Ò¥H¤u§@ªí1¤@¶}©l´N¬OActivate¤F~~~~
ºÉ¶q¤£­n¤@ª½¤Á´«¤u§@ªí.........§â¦Û¤v§Ë²V¶Ã¤F~~~§Ú¥ú¬Ý§Aªº»¡©ú...§Ú¦Û¤v¤]²V¶Ã¤FXD
¨Ì·Ó§Aªº¼gªk¡A§Ú»{¬°§AÁÙ¬Oª½±µ«ü©w­þ­Ó¤u§@ªí¤ñ¸û¦w¥þ^.^
§A¦bppt¤u§@ªíªº¾Þ§@¤ñ¸û¦h¡A«ö¶s«o³]¦b¤u§@ªí1ªº½T®e©ö¥X°ÝÃD

¦pªG¬O§Ú¡A´N³o¼Ë¼gºO~~~

With Sheets("PPT")
  If .[A1].Value <> "" Then .[A1:Z200].Delete Shift:=xlShiftUp    '²M°£©Ò¦³¸ê®Æ
  [A1].CurrentRegion.Copy
  .[A1].PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
  , SkipBlanks:=False, Transpose:=False
  Application.CutCopyMode = False
  A = .UsedRange.Rows.Count
  For i = 2 To A
    .Range("U" & i).Value = .Range("G" & i).Value + 1
  Next i
End With

¦^´_ 11# n7822123

³á~ ¨º§Úª¾¹D¤F  «ö¶s¦b­þ¤@­¶¡A«ö¤U«ö¶sªº®É­Ô¡A¨º¤@­¶´N¬OActivate¤F
¥ô¦ó«ü¥O©Î¬O¬Ù¤F "«e­±¦a§}" ªº¼gªk¡A³£¬O°w¹ï³o¤@­¶­±¦b°Ê§@

©Ò¥H¦pªG¤T­ÓSHEET¡A¤À§O¬O¡i¥\¯à°Ï¡j¡B¡iµ²ªG°Ï¡j¡B¡i¿é¥X°Ï¡j
«Ü¦h«ö¶s¥\¯à³£¬O¼g¦b¡i¥\¯à°Ï¡jªº¸Ü

§Ú¥u­n¼g
Range("A1:B5").select  ==>> ¡i¥\¯à°Ï¡jªºA1~B5·|³Q¿ï¨ú

¦pªG·Q­n°w¹ï¥t¥~¨â­Ó­¶­±¥h°µ³B²z
§Ú´N¥²¶·¥ý¼gWITH

With Sheets("µ²ªG°Ï")
.Range("A1:B5").select    ==>>  ¡iµ²ªG°Ï¡jªºA1~B5·|³Q¿ï¨ú    «e­±­n¥["ÂI"¡A¤~¬O°w¹ïWithªºSheet¨Ó°Ê§@

¨º¨Ó­Ó´ú¸Õ
¦pªGµ{¦¡¦p¤Uªº¸Ü
Range("A1:B5").select

With Sheets("µ²ªG°Ï")
.Range("A1:B5").select
End with

With Sheets("¿é¥X°Ï")
.Range("A1:B5").select
End with

ªGµM´N·|¨Ä¨Äªº¥h¿ï¨ú¦USheet¿ï¨ú¸Ó¹ïÀ³ªº°Ï¶ô
¡i¥\¯à°Ï¡jªºA1~B5·|³Q¿ï¨ú
¦A¨Ó¬O
¡iµ²ªG°Ï¡jªºA1~B5·|³Q¿ï¨ú
¦A¨Ó¬O
¡i¿é¥X°Ï¡jªºA1~B5·|³Q¿ï¨ú

­ì¨Ó¦³ÂI ¸ò¨S¦³ÂI  ÁÙ¦³WITH  ¬O³o¼Ë¥Î±o³á
§Ú·|¤F  YA~ ·PÁ¤j¤j±Ð¾Ç~