Dear brother and sister,
I have a new weekly task. I have a pivot table and one summary file (PCT-AFTER,xls). I asked here before, the forum is very helpful.
Task:
I need to get some data from pivot table and copied to the summary file according to specific account managers.
How can I modify the following code, therefore, I can copy data from pivot table to summary file correctly?
If you following code, it will shows the message : "Pivot table for processing is not found on activesheet of this workbook", I quite sure the sheet is "AD".
LMR file is pivot table
PCT-After.xls is summary file
My macro knowledge is week, I tried to modify for more than 10 times, still doesn't work. Please help me.
Option Explicit
Sub test()
Dim PCT As Workbook, main As Workbook, z(1 To 1, 1 To 5), i As Long, pctn, idate, pt As PivotTable, c, firstaddress As String, temp, irow As Long
On Error Resume Next: Set PCT = Workbooks("PCT-after.xls")
irow = Application.InputBox("Please enter row number for PCT-enter.xls to input data (same row is used for all students)", Type:=1)
If irow = 0 Then Exit Sub
If Err.Number <> 0 Then
MsgBox "Please open PCT-after.xls file and try again", vbCritical: Exit Sub
End If: Set main = ActiveWorkbook: Set pt = main.ActiveSheet.AD
If Err.Number <> 0 Then
MsgBox "Pivot table for processing is not found on activesheet of this workbook", vbCritical: Exit Sub
End If: Application.ScreenUpdating = False
With PCT.Sheets("Name List"): pctn = .Range(.[a5], .Cells(Rows.Count, "a").End(xlUp)): End With
With main.Sheets(1)
idate = .[a1:a999].Find("Report Date:", , xlValues, xlWhole).Offset(, 1):
With pt
For i = 1 To UBound(pctn)
Set c = .TableRange1.Find(pctn(i, 1), , xlValues, xlPart)
If Not c Is Nothing Then
firstaddress = c.Address
Do
If c.PivotCell.PivotCellType = xlPivotCellSubtotal Then
temp = Trim(pctn(i, 1)) & " 99999*": z(1, 1) = c.Offset(, 2): z(1, 2) = c.Offset(, 3)
z(1, 3) = .GetData("' % Primary sales of closed leads' 'Campaign Name' 'maturing mortgage' 'RM Name'" & temp)
z(1, 4) = .GetData("'% Primary sales of closed leads' 'Campaign Name' 'maturing term deposit' 'RM Name'" & temp)
z(1, 5) = .GetData("'% Primary sales of closed leads' 'Campaign Name' 'signficiant deposit' 'RM Name'" & temp)
With PCT.Sheets(Trim(pctn(i, 1))): With .Cells(irow, 1)
.Value = "'" & idate: With .Offset(, 25).Resize(, 5): .Value = z: .NumberFormat = "0.00%": End With
End With: End With: End If: Set c = .TableRange1.FindNext(c): Loop Until c Is Nothing Or c.Address = firstaddress
End If: Next: End With: End With: Application.ScreenUpdating = True: End Sub
Bookmarks