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

[µo°Ý] Ãö©ó¦Û°Ê¶K¤W³sµ²¦Ü¥t¤@­ÓÀɮרæ۰ʴ¡¤J¶W³sµ²

¦^´_ 10# lpk187
·PÁÂÀ°¦£~¨Ï¥Î¤W¤è«ü¥OŪ¨ú Q2¥Îcell(filename)¥s¥X¨Óªº¸ô®|¤£ª¾¬°¦ó·|°Ñ·Ó¿ù»~
©Ò¥H§Ú¥u¨Ï¥Î¨ì Address:=ThisWorkbook.Name  ³o¼Ë¤]¯à¹F¦¨§Ú·Q­nªº®ÄªG(¦p¤U¤è¥N½X)

¥t·Q¦Aª¾¹D¦p¦ó¦b
¥H¤U³o¥N½X¤¤ ´¡¤J³o¦ê¦Û°Ê¦b³Ì¤U¤èªÅ¥Õ¦C¶K¤Wªº«ü¥O©O
      EndRow = ActiveSheet.UsedRange.Rows.Count + 1 '©Ò¦³¨Ï¥Î¤¤ªº¦C¼Æ+1
    Rows(EndRow).PasteSpecial Paste:=xlPasteValues'¥Ø«e¨Ï¥Î¤¤ªº¤À­¶,ªº³Ì¤U¤èªÅ¥Õ¦C¶K¤W­È

¦]¦pªG§âRange("B7").Select®³±¼,Åܦ¨¬O¥H§Ú·Æ¹«ÂI¿ïªºÀx­È®æ¬°¶}ÀY¥h¶K¤W
¦pªG­n¥LÅý¥L¥HA1.A2.A3.A4³o¼Ë¦Û°Ê¨Ì§Ç©¹¤U¶K¤W¤£ª¾¸Ó¦p¦ó§ó§ï©O
  1. Sub ¶K¤W¸ê®Æ¦Ü_·~°ÈºÞ²zTEST¦Û°Ê©¹¤U¶K¤@¦æ()
  2. '
  3. ' ¶K¤W¸ê®Æ¦Ü_·~°ÈºÞ²z ¥¨¶°
  4. '

  5. '
  6.     Range("A3:P3").Select
  7.     Selection.Copy
  8.     Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Activate
  9.     Range("B7").Select
  10.     ActiveSheet.Paste Link:=True
  11.     Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Activate
  12.     ActiveSheet.Paste Link:=True
  13.     Application.CutCopyMode = False
  14.     ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=ThisWorkbook.Name

  15. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ starry1314 ©ó 2015-5-6 08:25 ½s¿è

¦^´_ 11# starry1314

¤£ª¾&!Q2¬O­n«ç»ò­×¥¿©O,
ª¬ªp:
1.°Ñ·Ó¸ô®|·|¿ù»~,
2.¤]·|¦b·~°È©ú²Ó·s¶K¤W·~°È©ú²ÓªºÀɮ׸ô®|

TOP

¦^´_ 12# starry1314
  1. Sub ¶K¤W¸ê®Æ¦Ü_·~°ÈºÞ²zTEST¦Û°Ê©¹¤U¶K¤@¦æ()
  2. '
  3. ' ¶K¤W¸ê®Æ¦Ü_·~°ÈºÞ²z ¥¨¶°
  4. '

  5. '
  6.    
  7.    
  8.     Range("A3:P3").Copy
  9.     Workbooks.Open (ThisWorkbook.Path & "\" & "«È¤á©ú²Ó-·~°È±M¥Î.xlsm")
  10.     Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Activate
  11.     EndRow = Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Sheets("¤­¤ë").Range("B4", Cells(Rows.Count, "B").End(xlUp).Address).Row + 1 '©Ò¦³¨Ï¥Î¤¤ªº¦C¼Æ+1
  12.     Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Sheets("¤­¤ë").Cells(EndRow, "B").PasteSpecial Paste:=xlPasteValues '¥Ø«e¨Ï¥Î¤¤ªº¤À­¶,ªº³Ì¤U¤èªÅ¥Õ¦C¶K¤W­È
  13.     ActiveSheet.Paste Link:=True
  14.     Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Activate
  15.     ActiveSheet.Hyperlinks.Add Anchor:=Cells(EndRow, "B"), Address:=ThisWorkbook.Name
  16.     Workbooks("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Close True

  17. End Sub
½Æ»s¥N½X

TOP

¦^´_ 12# starry1314


    ¸ô®|ªº°ÝÃD¡A­Y­n¥Îºô¸ôºÏºÐ¾÷¨º¸ô®|´N­n³]µ´¹ï¸ô®|µ¹¥¦
¦p¤U¹Ï§â¸ô®|½Æ»s°_¨ÓµM«á  Address:="½Æ»sªº¸ô®|" & "\" & ThisWorkbook.Name

a.png (49.26 KB)

a.png

TOP

¥»©«³Ì«á¥Ñ starry1314 ©ó 2015-5-6 08:40 ½s¿è

¦^´_ 14# lpk187

¤F¸Ñ~¨º
EndRow = Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").ActiveSheet.Range("b4", Cells(Rows.Count, "A").End(xlUp).Address).Row + 1 '©Ò¦³¨Ï¥Î¤¤ªº¦C¼Æ+1
   
°²¦pB4,B5¤w¦³¸ê®Æ~¥L·|Åܦ¨¦b·~°È±M¥ÎªºB5ªº¦ì¸mÂл\ ¤£·|¶]¨ìB6¥h©O
À³¸Ó¬ORange("b4",    Åý¥L¤@ª½­«B4+1§a?
©Ò¥H§Ú±N¥¦®³±¼§ï¦¨
EndRow = Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").ActiveSheet.Range( Cells(Rows.Count, "A").End(xlUp).Address).Row + 1 '©Ò¦³¨Ï¥Î¤¤ªº¦C¼Æ+1
§Y¥i¥H¦Û°Ê©¹¤U¤F
¦ý¤£ª¾¬°¦ó¦pªGÂIÀ»¶W³sµ²¤§«á¸õ¦^ ­Ó§O«È¤á©ú²Ó³£·|°Ñ·Ó¿ù»~...

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-5-6 08:44 ½s¿è

¦^´_ 12# starry1314


    µ{§Ç­×§ï¦p¤U
  1. Sub ¶K¤W¸ê®Æ¦Ü_·~°ÈºÞ²zTEST¦Û°Ê©¹¤U¶K¤@¦æ()
  2. '
  3. ' ¶K¤W¸ê®Æ¦Ü_·~°ÈºÞ²z ¥¨¶°
  4. '

  5. '
  6.    
  7.    
  8.     Range("A3:P3").Copy
  9.     Workbooks.Open ("½Æ»sªº¸ô®|" & "\" & "«È¤á©ú²Ó-·~°È±M¥Î.xlsm")
  10.     Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Activate
  11.     EndRow = Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Sheets("¤­¤ë").Range("B4", Cells(Rows.Count, "B").End(xlUp).Address).Row + 1 '©Ò¦³¨Ï¥Î¤¤ªº¦C¼Æ+1
  12.     Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Sheets("¤­¤ë").Cells(EndRow, "B").PasteSpecial Paste:=xlPasteValues '¥Ø«e¨Ï¥Î¤¤ªº¤À­¶,ªº³Ì¤U¤èªÅ¥Õ¦C¶K¤W­È
  13.     ActiveSheet.Paste Link:=True
  14.     Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Activate
  15.     ActiveSheet.Hyperlinks.Add Anchor:=Cells(EndRow, "B"), Address:="½Æ»sªº¸ô®|" & "\" & ThisWorkbook.Name
  16.     Workbooks("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Close True

  17. End Sub
½Æ»s¥N½X
¨ä¤¤ªºSheets("¤­¤ë")¡A§A¥i¥H¥ÎÅܼƨÓÅý¥¦Åܦ¨°ÊºAªº¡A©Î¬OÅý¨Ï¥ÎªÌ¦Û¤v¿ï¾Ü¡A¤]©Î¬O¨Ì·Ó¤é´Á¨Ó¿ï¾Ü¡A¤èªk¦³«Ü¦h¡A
ÁÙ¦³ActiveSheet¤]¥i¥H¥ÎSheets("¤­¤ë")ªºÅܼƨú¥N¤§
³s Range("A3:P3").Copy³o­Ó¤]¥i¥HÅý¥¦¦¨¬°ÅܼÆ
¦bµ{¦¡¤¤°£«D¥²­n¡A§_«hºÉ¶q¤£­n¥Î.Select©Î.Activate¡A¦]¬°³o¼Ë²£¥Íªºbug¤]·|«Ü¦hªº¡A¦Ó¥B°Ê§@¤]·|ÅܺC¡I

TOP

¦^´_ 15# starry1314


    ActiveSheet.Range("b4", Cells(Rows.Count, "A").End(xlUp).Address).Row + 1
³o¥yªº·N«ä¬O±q©~b4¶}©l¨ìBÄ檺³Ì«áÀx¦s®æµM«á¦A¥[1¬°¤U¤@­ÓÀx¦s®æ

TOP

¦^´_ 16# lpk187

»yªk¿ù»~À³¸Ó¬O³o¼Ë¤~¹ï
    EndRow = Workbooks("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Sheets("¤­¤ë").Cells(Rows.Count, "B").End(xlUp).Row + 1 '©Ò¦³¨Ï¥Î¤¤ªº¦C¼Æ+1

TOP

1.Sheets("¤­¤ë")<½Ð°ÝÃö©ó©p©Ò»¡ªºÅܼÆ
°²¦p¦b­Ó§O«È¤á©ú²Ó(Q3)°µ¤@­Ó²M³æ ¤@~¤Q¤G¤ë,¨º¦³¤°»ò«ü¥O¥i¥HÅý"¤­¤ë" ¥hŪ¨ú¨º­Ó¦ì¸mÅܦ¨°ÊºA©O?  cells(Q3) ³o¼Ë¶Ü?

Workbooks.Open ("½Æ»sªº¸ô®|" & "\" & "«È¤á©ú²Ó-·~°È±M¥Î.xlsm")
¦]«È¤á©ú²Ó-·~°È±M¥Î.xlsm·|¦³¦n´X­Ó·~°Èªº,¦p \\..\..\..\..\·~°È±M¥Î   ¤º¦³¤£¦P·~°Èªº¸ê®Æ§¨,¦A¤U¤@¼h¤~·|¬O«È¤á©ú²Ó-·~°È±M¥Î.xlsm

©Ò¥H¥i¥H¥Î
Workbooks.Open (" \\..\..\..\..\·~°È±M¥Î" &"¤£¦P·~°Èªº¸ê®Æ§¨(¨ÌO3¤º®eªº¦WºÙÅܧó)"& "\" & "«È¤á©ú²Ó-·~°È±M¥Î.xlsm")

¦p·~°È±M¥Î(½Æ»s¸ô®|)," &"·~°È-±i,  "& "\" & "«È¤á©ú²Ó-·~°È±M¥Î.xlsm"  ¹³³o¼Ë¥i¥H¹F¦¨¶Ü

TOP

¦^´_ 18# lpk187


ÂIÀ»¶W³sµ²«á   °Ñ·Ó¸ô®|¿ù»~ªº°ÝÃDª¾¹D­þÃä¥X°ÝÃD¤F
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=ThisWorkbook.Path & "\" & ThisWorkbook.Name, SubAddress:=ActiveSheet.Name

SubAddress:=ActiveSheet.Name     '¾É­P·~°È©ú²Óªº¶W³sµ²·|Ū¨ú·~°È©ú²Óªº¥Ø«e¤u§@­¶¦WºÙ,  Åܦ¨ ­Ó§O«È¤á©ú²Ó(¶W³sµ²)-¤­¤ë'

TOP

        ÀR«ä¦Û¦b : ¥Ç¿ù¥XÄb®¬¤ß¡A¤~¯à²M²bµL·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD