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

[µo°Ý] ¸ê®æµ²¤Jªí®æVBA

[µo°Ý] ¸ê®æµ²¤Jªí®æVBA

FORM.rar (7.72 KB) ¥i§_¥ÎVBA, ±N¤U¦C¸ê®Æ, µ²¦X¤Jªí®æ¤º, ¦Û°Ê²£¥Í¥HNAME¬°¦WºÙªºSHEET?


Name        Dept        Post
A        1        Clerk
B        2        Senior Clerk
C        3        Officer

¤U¦C¬°SHEET A, ±NNAME,DEPT, POST¤º®eÅã¥Ü, ¨Ã¦³¤U¦CRECORD,IN,OUT, REMARKSªº«ö®æ:
Name        A                        Post        Clerk
Dept        1                               
                                       
Record        In        Out        Remarks

¦^´_ 1# missbb
°õ¦æ«e¥u¦³data¤u§@ªí
  1. Sub Test()
  2.   Dim i As Long, bAlert As Boolean
  3.   Dim sName As String, sDept As String, sPost As String
  4.   Dim wsTemp As Worksheet
  5.   
  6.   '¥ý«Ø¥ß¼È®Éªº¤u§@ªí½d¥»¡A¨Ñ¤§«áCopy
  7.   Set wsTemp = Sheets.Add(After:=Sheets(Sheets.Count))
  8.   With wsTemp
  9.     .Cells(1, 1).Value = "Name"
  10.     .Cells(2, 1).Value = "Dept"
  11.     .Cells(1, 5).Value = "Post"
  12.     With .Cells(4, 1)
  13.       .Resize(1, 4).Value = Array("Record", "In", "Out", "Remarks")
  14.       .Resize(5, 4).Borders.LineStyle = xlContinuous
  15.     End With
  16.   End With
  17.   
  18.   '¹ï data ¤¤¶µ¥Ø«Ø¥ß¦U¤À­¶
  19.   With Sheets("data")
  20.     For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
  21.       sName = .Cells(i, 1).Value
  22.       sDept = .Cells(i, 2).Value
  23.       sPost = .Cells(i, 3).Value

  24.       wsTemp.Copy After:=Sheets(Sheets.Count)
  25.       With Sheets(Sheets.Count)
  26.         .Name = sName
  27.         .Cells(1, 2).Value = sName
  28.         .Cells(2, 2).Value = sDept
  29.         .Cells(1, 6).Value = sPost
  30.       End With
  31.     Next
  32.   End With
  33.   
  34.   bAlert = Application.DisplayAlerts
  35.   Application.DisplayAlerts = False
  36.   wsTemp.Delete
  37.   Application.DisplayAlerts = bAlert
  38.   
  39. End Sub
½Æ»s¥N½X
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 2# stillfish00


¦hÁ«ü¾É!

TOP

        ÀR«ä¦Û¦b : ¡i¦æµ½­n¤Î®É¡j¦æµ½­n¤Î®É¡A¥\¼w­n«ùÄò¡C¦p¿N¶}¤ô¤@¯ë¡A¥¼¿N¶}¤§«e¤d¸U¤£­n°±º¶¤õ­Ô¡A§_«h­«¨Ó´N¤Ó¶O¨Æ¤F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD