[µo°Ý] ¦p¦ó¨ú±osheet1«ü©w¤º®e½Æ»s¨ìsheet2

coke373

Sheet1¬O¨C¤Ñ·|§ó·s¸ê®Æ.
§Ú·Q¨úsheet1 A:Aªº«ü©w¤º®e,½Æ»s¨ìSheet2ªºªí®æ.
¦]¬°¬O¨C¤Ñ³£¨úSheet1A:Aªº«ü©w¤º³¡,·Q§Q¥ÎVABÀ°¦£§¹¦¨.
³]©w½Æ»s¨ìsheet2¥i¥H«ö¤é´Á©«¤W.2017/4/1-2017/4/30

coke373

Sub nn()
  Dim lSRow&, lTRow&
  Dim rTar As Range
  Dim wsTar As Worksheet
  
  Set wsTar = Sheets("Sheet2")
  
  With Sheets("Sheet1")
    wsTar.Cells.Clear
    .[A1:D2].Copy wsTar.[A1]
    lTRow = 7
    lSRow = 3
    Set rTar = .Cells(lTRow, 1)
    While rTar + rTar.Offset(, 1) + rTar.Offset(, 2) + rTar.Offset(, 3) <> 0
      rTar.Resize(, 4).Copy wsTar.Cells(lSRow, 1)
      lSRow = lSRow + 1
      lTRow = lTRow + 5
      Set rTar = .Cells(lTRow, 1)
    Wend
  End With
End Sub

§Ú¦³ª¦¤å¡A¤Þ¥Î¤F³o­ÓVBA¡A¦ý¬O§Ú¨C¤Ñ³£·|¦bSheet1§ó·s
§Ú§â³o¥ywsTar.Cells.Clear§R°£¤F¡A«ç¼Ë¥i¥H¨C¤Ñ¸õ¤@¦æ¡A¤µ¤ÑA1©«¤W¡A©ú¤ÑB1©«¤W¡A¦p¦¹Ãþ±À¡C
¦]¬°¬O¾ã­Ó¤ë³£­n­«½Æ¨úA:Aªº¸ê®Æ¡A©«¤WSheet2¡A¤@¦C¥Nªí¤@¤Ñ¡C

coke373

EndRow = ActiveSheet.UsedRange.Rows.Count + 1 '©Ò¦³¨Ï¥Î¤¤ªº¦C¼Æ+1
§Úª¦¤å§ä¨ì³o¤@¥y¡C½Ð°Ý¦U¦ì¤j¤j¡A³o¤@¥y¹ï¤£¹ï¡HÁÙ¦³©ñ¦b¨º¤@¬q¡H

joey0415

¦^´_ 3# coke373

°Ñ¦Ò°Ñ¦Ò

1. Sub ex()
   
2. 
   
3. k = Sheets(2).Cells(9, Columns.Count).End(xlToLeft).Column
   
4. For i = 1 To 40
   
5.    Sheets(2).Cells(i + 1, k + 1) = Sheets(1).Cells(i, 1)
   
6. Next
   
7.    Sheets(2).Cells(1, k + 1) = Format(Year(Date), "0000") & Format(Month(Date), "00") & Format(Day(Date), "00")
   
8. 
   
9. End Sub

½Æ»s¥N½X

coke373

¦^´_ 4# joey0415


¤j¤j ÁÂÁ§AÀ°¦£
½Ð°Ý¥i¤£¥i¥H¦bI=1-40 §ï¦¨«ü©wªº¡A¨Ò¦p¡GcopyC13:C98¡A¦A¥[¤WC107:C120¡A¦A¤W¥[¤WC138:C140¡C
½Ð°Ý«ç¼Ë§ï¡C

coke373

¦^´_ 4# joey0415


    Sub ex()

k = Sheets(2).Cells(9, Columns.Count).End(xlToLeft).Column
For i = 1 To 40
   Sheets(2).Cells(i + 1, k + 1) = Sheets(1).Cells(i, 1)
Next
   Sheets(2).Cells(1, k + 1) = Format(Year(Date), "0000") & Format(Month(Date), "00") & Format(Day(Date), "00")

End Sub

¤j¤j³o­Ó¤èªk¬O¥i¦æ¡A¦ý¬OFor i = 1 To 40 ³o¤@¦æ¥i§_§ï¦¨¨ä¥L¡A¦]¬°sheet1¬O¤@¥÷report¡A¬O¤@­Óµ{¦¡Åª¨ú¥X¨Óªº¡A¥LŪ¨ú¨Ó¦³«Ü¦hÁôÂîæ¡A¦ý¬O§Ú­n¤º®e­è¦n¦bC11:C96,C107:C120,C137:C139¡C§Ú¹Á¸Õ§âFor i = 1 To 40 §ï¦¨ For i = C11 to C139¡A­ì¨Ó¬O¤£¦æ¡A¥Xerror¡C½Ð®v¥SÀ°¦£¡C

joey0415

¦^´_ 6# coke373

Book1.zip (9.14 KB)

¤U¸ü¦¸¼Æ: 8

2017-4-4 09:44

1. Sub ex()
   
2. 
   
3.     k = Sheets(2).Cells(9, Columns.Count).End(xlToLeft).Column
   
4.    
   
5.     For i = 11 To 96
   
6.        Sheets(2).Cells(i - 9, k + 1) = Sheets(1).Cells(i, 3)
   
7.     Next
   
8.    
   
9.     For i = 107 To 120
   
10.        Sheets(2).Cells(i - 19, k + 1) = Sheets(1).Cells(i, 3)
    
11.     Next
    
12.    
    
13.     For i = 137 To 139
    
14.        Sheets(2).Cells(i - 35, k + 1) = Sheets(1).Cells(i, 3)
    
15.     Next
    
16.    
    
17.     Sheets(2).Cells(1, k + 1) = Format(Year(Date), "0000") & Format(Month(Date), "00") & Format(Day(Date), "00")
    
18. 
    
19. End Sub

½Æ»s¥N½X

coke373

¦^´_ 7# joey0415


    ¤j¤j·PÁ§AªºÀ°¦£!¤µ±ß¦^¨ì¤½¥q¦A®M¥Î.

coke373

¦^´_ 7# joey0415
¤j¤jÁÂÁ§AÀ°¦£
§Ú¦³´X­Ó°ÝÃD·Q°Ý

Sheets(2).Cells(i - 9, k + 1) = Sheets(1).Cells(i, 3)
Sheets(2).Cells(i - 19, k + 1) = Sheets(1).Cells(i, 3¡^
Sheets(2).Cells(i - 35, k + 1) = Sheets(1).Cells(i, 3)  
i-9
i-19
i-35 ¬O¥Nªí¤°»ò¡H
For i = 11 To 96 ¦pªG§Ú§ï¦¨For i = 11 To 100 ¹w¦h¦æ¼Æ¡C­n¤£­n­×§ïi-9?
¦]¬°§Ú¹Á¸Õ­×§ïFor i = 11 To 100 ¡AµM«á¤U­±ªºFor i = 107 To 120 ©«¥X¨Ó·|´î¤Ö¤F¡C

joey0415

¦^´_ 9# coke373

i- ?
¥Nªí§Aªºªí®æ©¹¤W²¾°Ê¦h¤Ö¡A¦Û¦æ·L½Õ
­Y¥u¦³i  ¨º¥Nªí½Æ»sªº¦ì¸m»P­ì¨Óªº¦ì¸m¬Û¦P

½Ð¦Û¦æ¬ã¨s¬Ý¬Ý

¤U¸ü (42.69 KB)

2017-4-5 09:54