- ©«¤l
- 354
- ¥DÃD
- 5
- ºëµØ
- 0
- ¿n¤À
- 387
- ÂI¦W
- 0
- §@·~¨t²Î
- windows7
- ³nÅ骩¥»
- vba,vb,excel2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2017-1-8
- ³Ì«áµn¿ý
- 2024-8-2
|
©êºp¦³ÂI¬~¤å³¹¤F
¦pªG»¡ §AªºÀɦW ¤£n¦³order1ªº¸Ü
¦bsaveas ¨ºÓ¦ì¸m,§â & ¥XÀɸ¹ µ¹§R±¼
¤U±³o¼Ë- Sub ¦Û°Ê·s¼W¤é¤u§@ï§ó·s()
- Application.DisplayAlerts = False
- Application.ScreenUpdating = False
- ¥»ÀɦW = ActiveWorkbook.Name
- ¥»¸ô®| = ActiveWorkbook.Path
- ©±¦W¶}ÀY¯Á¤Þ = "A©±001"
- '***********************************************§ä¥Xa©±001¦ì¸m***********************************
- For aaa = 1 To 20
- On Error Resume Next
- ©±¦WÄæ¦ì = Sheets("Total").Rows(aaa).Find(What:=©±¦W¶}ÀY¯Á¤Þ, LookIn:=xlValues, SearchDirection:=xlNext).Column
- On Error GoTo 0
- Next
- For aaa = 1 To ©±¦WÄæ¦ì
- On Error Resume Next
- ©±¦W¦C¦ì = Sheets("Total").Columns(aaa).Find(What:=©±¦W¶}ÀY¯Á¤Þ, LookIn:=xlValues, Lookat:=xlWhole, SearchDirection:=xlNext).Row
- On Error GoTo 0
- Next
- '***********************************************§ä¥Xa©±001¦ì¸m***********************************
- '***********************************************§ä¥X³Ì«á¤@¶¡©±¦ì¸m***********************************
- ©±¦W³Ì«áÄæ¦ì = Sheets("Total").Rows(©±¦W¦C¦ì).Find(What:="*", LookIn:=xlValues, Lookat:=xlWhole, SearchDirection:=xlPrevious).Column
- '***********************************************§ä¥X³Ì«á¤@¶¡©±¦ì¸m***********************************
- '***********************************************§ä¥X²£«~¼Æ¶q***********************************
- ²£«~codeÄæ = Sheets("Total").Rows(©±¦W¦C¦ì).Find(What:="²£«~CODE", LookIn:=xlValues, Lookat:=xlWhole, SearchDirection:=xlPrevious).Column
- ²£«~code¦C = Sheets("Total").Columns(²£«~codeÄæ).Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious).Row
- ²£«~Á`¼Æ = ²£«~code¦C - ©±¦W¦C¦ì
- '***********************************************§ä¥X²£«~¼Æ¶q***********************************
- '***********************************************Åý¤§«á°j°é¥Î***********************************
- ²£«~°_©l¦C©w¦ì = ©±¦W¦C¦ì + 1
- ²£«~µ²§ô¦C©w¦ì = ²£«~code¦C
- '***********************************************Åý¤§«á°j°é¥Î***********************************
- ''*************************¨ä¥L¸ê°T*************************
- ¤é´Á¼È¦s = Workbooks(¥»ÀɦW).Sheets("Total").[G1]
- ©±®a·s¼W¼Æ¶q = 0
- ©±®a·s¼W©±¦W = "·s¼W©±¦W:"
- ''*************************¨ä¥L¸ê°T*************************
- '*************************¬y¤ô¸¹°j°é°_©lÈ*************************
- ¬y¤ô = 1
- '*************************¬y¤ô¸¹°j°é°_©lÈ*************************
- While ©±¦WÄæ¦ì < ©±¦W³Ì«áÄæ¦ì + 1
- If UCase(Workbooks(¥»ÀɦW).Sheets("Total").Cells(7, ©±¦WÄæ¦ì)) = "K" Then '°²¦p¬OkÈ ´N¤£·|·s¼W
- ©±¦W = Workbooks(¥»ÀɦW).Sheets("Total").Cells(©±¦W¦C¦ì, ©±¦WÄæ¦ì)
- ©±®a·s¼W©±¦W = ©±®a·s¼W©±¦W & vbCrLf & ©±¦W
-
- ¥XÀɸ¹ = "order" & ¬y¤ô
- Set nX = Workbooks.Add
- Application.DisplayAlerts = False
-
- If ActiveWorkbook.Sheets.Count = 3 Then
- ActiveWorkbook.Sheets(3).Delete
- End If
- If ActiveWorkbook.Sheets.Count = 2 Then
- ActiveWorkbook.Sheets(2).Delete
- End If
- ActiveWorkbook.Sheets(1).Name = ©±¦W
- ²£«~¹Bºâ¦C = ²£«~°_©l¦C©w¦ì
- ²£«~°±ºâ¦C = ²£«~µ²§ô¦C©w¦ì
- orderrow = 1
- While ²£«~¹Bºâ¦C < ²£«~°±ºâ¦C + 1
- If Workbooks(¥»ÀɦW).Sheets("Total").Cells(²£«~¹Bºâ¦C, ©±¦WÄæ¦ì) <> "" Then
-
- ActiveWorkbook.Sheets(1).Cells(orderrow, 1) = "DK"
- ActiveWorkbook.Sheets(1).Cells(orderrow, 3) = ¤é´Á¼È¦s
- ActiveWorkbook.Sheets(1).Cells(orderrow, 4) = "DN"
- ActiveWorkbook.Sheets(1).Cells(orderrow, 5) = "B99"
- ActiveWorkbook.Sheets(1).Cells(orderrow, 6) = "©±¦W"
- ActiveWorkbook.Sheets(1).Cells(orderrow, 7) = Workbooks(¥»ÀɦW).Sheets("Total").Cells(²£«~¹Bºâ¦C, ²£«~codeÄæ).Value
- ActiveWorkbook.Sheets(1).Cells(orderrow, 8) = Workbooks(¥»ÀɦW).Sheets("Total").Cells(²£«~¹Bºâ¦C, ²£«~codeÄæ + 3).Value
- ActiveWorkbook.Sheets(1).Cells(orderrow, 9) = Workbooks(¥»ÀɦW).Sheets("Total").Cells(²£«~¹Bºâ¦C, ©±¦WÄæ¦ì)
- orderrow = orderrow + 1
- End If
-
-
- ²£«~¹Bºâ¦C = ²£«~¹Bºâ¦C + 1
- Wend
- nX.SaveAs ThisWorkbook.Path & "/" & ©±¦W
- nX.Close
- ©±®a·s¼W¼Æ¶q = ©±®a·s¼W¼Æ¶q + 1
- End If
- ¬y¤ô = ¬y¤ô + 1
- ©±¦WÄæ¦ì = ©±¦WÄæ¦ì + 1
- Wend
- MsgBox "¦@" & ©±®a·s¼W¼Æ¶q & "©±®a¸ê®Æ·s¼W" & vbCrLf & ©±®a·s¼W©±¦W
- End Sub
½Æ»s¥N½X |
|