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

½Ð°Ý¼Ï¯Ã«á¦p¦ó§Ö³tÂ^¨ú©Ò»ÝªºÄæ¦ì¶K¦b¥t­ÓÀÉ®×

½Ð°Ý¼Ï¯Ã«á¦p¦ó§Ö³tÂ^¨ú©Ò»ÝªºÄæ¦ì¶K¦b¥t­ÓÀÉ®×

½Ð°Ý¦³¤@­Ó³øªí(¦pªþ¥ó)¦b9¤ë¥÷Äæ¦ì,¬O¨Ì¾Ú¸ê®ÆÀɪº¼Ï¯ÃSHEET¶K¦Ü³øªíÀɦӨӦӨÓ
¦]¬°¨C­Ó¤ë³£­nupdate QTYªº¸ê®Æ,
¦³¨S¦³¤°»ò¤è¦¡¦b¸ê®ÆÀɼϯ繫á,¥i¥H¦b¶}±Ò"³øªí"ÀÉ®×®É,¤U«ü¥O»Ý­nupdateªº¤ë¥÷«á,´N¥i¦Û°Êupdate¸ê®Æ?
ÁÂÁÂ


³øªí.zip (7.08 KB) ¸ê®ÆÀÉ.zip (11.61 KB)
Jessica

¦^´_ 1# jessicamsu

«ö"F5"Áä

TOP

¦^´_ 1# jessicamsu
³øªí¬¡­¶Ã¯ªºThisWorkbook¼Ò²Õ
  1. Private Sub Workbook_Open()
  2. Dim PT As PivotTable, A As Range, s%, yn As Boolean, k%, Wb As Workbook, fs$
  3. Sheets("Sheet1").Columns("A:D").Clear
  4. fs = ThisWorkbook.Path & "\¸ê®ÆÀÉ.xlsx"
  5. Set Wb = Workbooks.Open(fs)
  6. Set PT = Wb.Sheets("¼Ï¯Ã").PivotTables(1)
  7. With PT
  8. 10
  9. s = InputBox("¿é¤J¤ë¥÷", , 9)
  10. If IsNumeric(Application.Match(s, .ColumnFields("¤ë¥÷").DataRange, 0)) Then
  11. s = Application.Match(s, .ColumnFields("¤ë¥÷").DataRange, 0)
  12. Else
  13. MsgBox "µL¦¹¤ë¥÷": GoTo 10
  14. End If
  15. For Each p In Array("¦a°Ï§O", "«È¤á", "²£«~")
  16. Set A = .PivotFields(p).DataRange
  17. If Application.CountBlank(A) > 0 Then yn = True Else yn = False
  18. Sheets("Sheet1").[A1].Offset(, k).Resize(A.Rows.Count, 1).Value = A.Value
  19. If yn = True Then Sheets("Sheet1").[A1].Offset(, k).Resize(A.Rows.Count, 1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
  20. Sheets("Sheet1").[A1].Offset(, k).Resize(A.Rows.Count, 1) = Sheets("Sheet1").[A1].Offset(, k).Resize(A.Rows.Count, 1).Value
  21. k = k + 1
  22. Next
  23. Sheets("Sheet1").[A1].Offset(, k).Resize(A.Rows.Count, 1).Value = .DataBodyRange.Columns(s).Value
  24. End With
  25. Wb.Close 0
  26. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¡i®É¤é²öªÅ¹L¡j¤@­Ó¤H¦b¥@¶¡°µ¤F¦h¤Ö¨Æ¡A´Nµ¥©ó¹Ø©R¦³¦hªø¡C¦]¦¹¥²¶·»P®É¶¡Ävª§¡A¤Á²ö¨Ï®É¤éªÅ¹L¡C
ªð¦^¦Cªí ¤W¤@¥DÃD