Hsieh

¦^´_ 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