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

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

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

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

  4.     'Range("A3:P3").Copy
  5.     sht = ActiveSheet.Name
  6.     arr = Range("A3:P3")
  7.     ¥»Àɮתº¸ô®| = ThisWorkbook.Path & "\"
  8.     ¥»ÀɮתºÀɦW = ThisWorkbook.Name
  9.    
  10.     Workbooks.Open ("½Æ»sªº¸ô®|" & "\" & "«È¤á©ú²Ó-·~°È±M¥Î.xlsm") '³o¸Ìªº"½Æ»sªº¸ô®|"¬O­n¥i¥H¶}"«È¤á©ú²Ó-·~°È±M¥Î.xlsm"ªº¸ô®|
  11.     Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Activate
  12.     AA = Workbooks("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Sheets("¤­¤ë").Cells(Rows.Count, "B").End(xlUp).Address
  13.     EndRow = Workbooks("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Sheets("¤­¤ë").Cells(Rows.Count, "B").End(xlUp).Row + 1 '©Ò¦³¨Ï¥Î¤¤ªº¦C¼Æ+1
  14.     'Cells(EndRow, "B").PasteSpecial Paste:=xlPasteValues '­ì¥»ªºµ{§Ç
  15.     Cells(EndRow, "B").Resize(1, UBound(arr, 2)) = Application.Transpose(Application.Transpose(arr)) '¥Ø«e¨Ï¥Î¤¤ªº¤À­¶,ªº³Ì¤U¤èªÅ¥Õ¦C¶K¤W­È
  16.     'ActiveSheet.Paste Link:=True
  17.     Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Activate
  18.     'ActiveSheet.Hyperlinks.Add Anchor:=Cells(EndRow, "B"), Address:=ThisWorkbook.Name, SubAddress:=sht'­ì¥»ªºµ{§Ç
  19.     Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Hyperlinks.Add Anchor:=Cells(EndRow, "B"), Address:=¥»Àɮתº¸ô®| & ThisWorkbook.Name, SubAddress:=shtÅܧó«áªº '³o¸Ìªº¥»Àɮתº¸ô®|«h¬O³o­ÓÀɮשҦbªº¸ô®|
  20.     Workbooks("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Close True

  21. End Sub
½Æ»s¥N½X
¤W­±¬O§Ú¦³­×§ïªºµ{§Ç¡A¨ä¤¤¦³¸ÑÄÀ¸ô®|

TOP

¦^´_ 21# lpk187


    ¤£¦n·N«ä~
Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Hyperlinks.Add Anchor:=Cells(EndRow, "B"), Address:=¥»Àɮתº¸ô®| & ThisWorkbook.Name, SubAddress:=shtÅܧó«áªº '³o¸Ìªº¥»Àɮתº¸ô®|«h¬O³o­ÓÀɮשҦbªº¸ô®|

¦¹Äæ¤@ª½¶]¥X°»¿ù,¦ý¦b«e¤@­Ó©|¥¼­×¥¿¹Lªºµ{¦¡ ³o¼Ëªº¼gªk¬O¥¿±`¨Ï¥Î©O

TOP

¦^´_ 22# starry1314


    ¤£¦n·N«ä¡A¤W¯Z¤¤¡A¤£¯à§Y®É¦^À³¡A¤]¨S¦n¦nªºÀˬdµ{§Ç¬O§_¿ù»~¡I©êºp
  1. Sub ¶K¤W¸ê®Æ¦Ü_·~°ÈºÞ²zTEST¦Û°Ê©¹¤U¶K¤@¦æ()
  2. '
  3. ' ¶K¤W¸ê®Æ¦Ü_·~°ÈºÞ²z ¥¨¶°

  4.     'Range("A3:P3").Copy
  5.     sht = ActiveSheet.Name
  6.     arr = Range("A3:P3")
  7.     ¥»Àɮתº¸ô®| = ThisWorkbook.Path & "\"
  8.     ¥»ÀɮתºÀɦW = ThisWorkbook.Name
  9.     ¤é´Á = Format([B3], "M""¤ë")
  10.     Workbooks.Open (ThisWorkbook.Path & "\" & "«È¤á©ú²Ó-·~°È±M¥Î.xlsm") '³o¸Ìªº"½Æ»sªº¸ô®|"¬O­n¥i¥H¶}"«È¤á©ú²Ó-·~°È±M¥Î.xlsm"ªº¸ô®|
  11.     Windows("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Activate
  12.    
  13.     EndRow = Workbooks("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Sheets("¤­¤ë").Cells(Rows.Count, "B").End(xlUp).Row + 1 '©Ò¦³¨Ï¥Î¤¤ªº¦C¼Æ+1
  14.     'Cells(EndRow, "B").PasteSpecial Paste:=xlPasteValues '­ì¥»ªºµ{§Ç
  15.     Cells(EndRow, "B").Resize(1, UBound(arr, 2)) = Application.Transpose(Application.Transpose(arr)) '¥Ø«e¨Ï¥Î¤¤ªº¤À­¶,ªº³Ì¤U¤èªÅ¥Õ¦C¶K¤W­È
  16.     'ActiveSheet.Paste Link:=True
  17.     Workbooks("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Activate
  18.     'ActiveSheet.Hyperlinks.Add Anchor:=Cells(EndRow, "B"), Address:=ThisWorkbook.Name, SubAddress:=sht'­ì¥»ªºµ{§Ç
  19.    
  20.     Workbooks("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Sheets("¤­¤ë").Hyperlinks.Add Anchor:=Cells(EndRow, "B"), Address:=¥»ÀɮתºÀɦW, SubAddress:=sht 'Åܧó«áªº '³o¸Ìªº¥»Àɮתº¸ô®|«h¬O³o­ÓÀɮשҦbªº¸ô®|
  21.     Workbooks("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Close True

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

TOP

¦^´_ 23# lpk187

SubAddress:=sht & "!Q2" ¦A­×§ï     

TOP

¤£·|°Õ~¬O§Ú¤~¤£¦n·N«ä¡I¤@ª½³Â·Ð§A
¥ýºCºC´ú¸Õ¦A²z¸ÑÅo

TOP

¦^´_ 19# starry1314

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

¦^µª¤W­±³o°ÝÃD¡G
A1.¤§«e§Ú¦³°µ¤@­ÓÃþ¦üªºÀɮסA¦ý¦]¬°¦Ò¼{¨Ï¥Î®É¶¡ªº°ÝÃD(¤£¥Î¨C¦~ªº¤@¶}©l´N­×§ïµ{¦¡©Î§ó§ïÀɮצWºÙ¥H¤Î«Ü¦h¬ÛÃöªº°ÝÃD)¡A©Ò¥H§Ú§ï¥Î¸ê®Æ®wªº°µªk¡A§â¹³§A¤ë¥÷ªº¤u§@ªí§ï¦¨¥u¦³¥Î¤@­Ó¸ê®Æ®wªº¤u§@ªí
µM«á¦A°µ¤@­Ó¥i¥H¬d¸ßªº¤u§@ªí¨Ó¬d¸ß¬ÛÃöªº¥Øªº¡A·íµM§Ú¦b¸ê®Æ®w¦³©ñ©Ò¿×¦~«×¤ë¥÷ªºÄæ¦ì¡A¥H§Q¬d¸ß¥Î¡C
©Ò¥H¹³Sheets("¤­¤ë")°µÅܼơA«h¬O­n²o¯A¨ì¨Ï¥Îªº¥Øªº¤~¯à¤U¤èªk¡A¦Ó§Ú°µªº¥¨¶°¬O¥Î¤u§@ªí¨Æ¥ó¨Ó¥N´À§Aªº«ö¶s¡A¦Ó¥B§â¤ë¥÷ªº¼Æ¦r§ï¦¨ªü©Ô§B¼Æ¦r¡A³o¼Ë«h¥i¥H¥Î§A¤é´ÁÄæ¨Ó§ì¨ú¤ë¥÷¨Ó°µÅܼƨҦp
¤é´Á = Format([B3], "M""¤ë")

A2.­Y¦bQ3°µ²M³æ³o¤]¬O¤@­Ó¦n¤èªk¡A¨äÅܼƪ½«ü¥¦´N¥i¥H¦p¡Gxxx=Range("Q3") ©Îxxx=[Q3]<<³o¸Ì­n¥Î¤¤¬A¸¹(§AÀɮפ¤ªº²Ä¤@­Ó¥¨¶°¥Î¤p¬A¸¹´N¤£¹ï¤F)¡A¤S©Î¬Oxxx=Cells(3,"Q")¡A"¤£¬Ocells(Q3)"

TOP

Workbooks("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Sheets("¤­¤ë").Hyperlinks.Add
­×§ï¹L
Workbooks("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Sheets([Q3]).Hyperlinks.Add
³oÃä­n¦p¦óÂনÅܼƩO¡H¡@¤W¤è³£´«¹L¦ýÁÙ¬OµLªk°õ¦æ~
ÁÙ¬O»¡¤é´Á = Format([B3], "M""¤ë")
§Ú¥u­n¦Asheets(¤é´Á) ³o¼Ë¶Ü?

TOP

¦^´_ 10# lpk187


    ½Ð°Ý Windows("¥Ø«e©Ò¦bÀɦW.xlsm").Activate
¥i§ó§ï¬°
Windows("ThisWorkbook.Name").Activate ¶Ü?

TOP

¦^´_ 27# starry1314
¥i¸Õ¸Õ¬Ý
  1. Sub ¶K¤W¸ê®Æ¦Ü_·~°ÈºÞ²zTEST¦Û°Ê©¹¤U¶K¤@¦æ()
  2. '
  3. ' ¶K¤W¸ê®Æ¦Ü_·~°ÈºÞ²z ¥¨¶°
  4. '
  5.     Dim lSourceRow As Long, lTargetRow As Long
  6.     Dim wsTarget As Worksheet
  7.    
  8.     With ActiveSheet
  9.         lSourceRow = .Buttons(Application.Caller).TopLeftCell.Row   '³QÂIÀ»ªº¸Ó«ö¶s¦æ¼Æ
  10.         Set wsTarget = Workbooks("«È¤á©ú²Ó-·~°È±M¥Î.xlsm").Sheets(GetMonthStr(.Cells(lSourceRow, "B"))) '¤é´Á§PÂ_­n¶K¤Wªº¤u§@ªí
  11.         lTargetRow = wsTarget.Cells(Rows.Count, "B").End(xlUp).Row + 1  '­n¶K¤Wªº¦ì¸m
  12.         .Range(.Cells(lSourceRow, "A"), .Cells(lSourceRow, "P")).Copy wsTarget.Cells(lTargetRow, "B") '½Æ»s¶K¤W¸Ó¦æ¸ê®Æ
  13.     End With
  14.    
  15.     'QÄæ³]©w¶W³sµ²¨ì¥»ÀÉ®×
  16.     With wsTarget
  17.         .Hyperlinks.Add Anchor:=.Cells(lTargetRow, "Q"), _
  18.                         Address:=ThisWorkbook.FullName, _
  19.                         SubAddress:=ThisWorkbook.ActiveSheet.Name & "!" & Rows(lSourceRow).Address, _
  20.                         TextToDisplay:=.Cells(lTargetRow, "Q").Text
  21.     End With
  22. End Sub

  23. '¤u§@ªí¥Î¤¤¤å¦r­n¦Û¤v¼gfunctionÂà¡A¦pªG¬O1¤ë¡B2¤ë...¥i¥Î Format(¤é´Á,"m¤ë")Âà´«¸û¤è«K
  24. Function GetMonthStr(inDate As Date) As String
  25.     Select Case Month(inDate)
  26.         Case 1
  27.             GetMonthStr = "¤@¤ë"
  28.         Case 2
  29.             GetMonthStr = "¤G¤ë"
  30.         Case 3
  31.             GetMonthStr = "¤T¤ë"
  32.         Case 4
  33.             GetMonthStr = "¥|¤ë"
  34.         Case 5
  35.             GetMonthStr = "¤­¤ë"
  36.         Case 6
  37.             GetMonthStr = "¤»¤ë"
  38.         Case 7
  39.             GetMonthStr = "¤C¤ë"
  40.         Case 8
  41.             GetMonthStr = "¤K¤ë"
  42.         Case 9
  43.             GetMonthStr = "¤E¤ë"
  44.         Case 10
  45.             GetMonthStr = "¤Q¤ë"
  46.         Case 11
  47.             GetMonthStr = "¤Q¤@¤ë"
  48.         Case 12
  49.             GetMonthStr = "¤Q¤G¤ë"
  50.     End Select
  51. End Function
½Æ»s¥N½X
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

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

¦^´_ 29# stillfish00


  ¤£¦n·N«ä~¨Ï¥Îµ²ªG¨S¦³¤ÏÀ³©ö¨S¦³¸õ¥X°»¿ù.....
¥t·Q°Ý¤@¤U
lSourceRow = .Buttons(Application.Caller).TopLeftCell.Row   '³QÂIÀ»ªº¸Ó«ö¶s¦æ¼Æ
¬O·Æ¹«ÂIÀ»ªº©Ò¦b¦ì¸mªº¦æ¼Æ¶Ü?
ÁÙ¬O«ö¶s©Ò¦bªº¦æ¼Æ?

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¨S¦³©Ò¦³Åv¡A¥u¦³¥Í©Rªº¨Ï¥ÎÅv¡C
ªð¦^¦Cªí ¤W¤@¥DÃD