USE Vba to navigate through the pivot table when copying a single field to a separate sheet
I am using Excel 2013 and I have a pivot table with hundreds of filter values that I need to iterate through, making each one visible individually, then copy the filtered value and specific cell (non-Pivot and IF> 0) and paste it (value only) into the specified sheet and go to the next turning point and do the same. I found a code that is similar to what I want
Sub PivotStockItems()
Dim i As Integer
Dim sItem As String
Application.ScreenUpdating = False
With ActiveSheet.PivotTables("PivotTable1")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
.PivotCache.Refresh
With .PivotFields("Country")
'---hide all items except item 1
.PivotItems(1).Visible = True
For i = 2 To .PivotItems.Count
.PivotItems(i).Visible = False
Next
For i = 1 To .PivotItems.Count
.PivotItems(i).Visible = True
If i <> 1 Then .PivotItems(i - 1).Visible = False
sItem = .PivotItems(i)
Cells.Copy
Workbooks.Add
With ActiveWorkbook
.Sheets(1).Cells(1).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats
.SaveAs "C:\TEST\MyReport-" & sItem & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook
.Close
End With
Next i
End With
End With
End Sub Still I Know What I Need To Cut
Cells.Copy
Workbooks.Add
With ActiveWorkbook
.Sheets(1).Cells(1).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats
.SaveAs "C:\TEST\MyReport-" & sItem & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook
.Close
I just don't know what to add to copy a specific cell (non-Pivot) and paste it into another sheet if it meets criteria> 0. I am relatively new to VBA and I am trying to improve my skills.
Adding screenshots for reference Basically, I want to iterate through B3 (pivot table) and copy B3 and F46 into the new sheet shown below. If F46> 0.
Thank.
source to share
This should work for you. You will need to adjust the titles of the reference and information sheets as indicated below.
Sub PivotStockItems()
Dim i As Integer
Dim sItem As String
Dim pivotSht As Worksheet, dataSht As Worksheet
Set pivotSht = Sheets("test") 'adjust to the name of sheet containing your pivot table
Set dataSht = Sheets("SKUS_With_Savings") 'as per your image
Application.ScreenUpdating = False
With pivotSht.PivotTables("PivotTable1")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
.PivotCache.Refresh
With .PivotFields("Yes")
'---hide all items except item 1
.PivotItems(1).Visible = True
For i = 2 To .PivotItems.Count
.PivotItems(i).Visible = False
Next
For i = 1 To .PivotItems.Count
.PivotItems(i).Visible = True
If i <> 1 Then .PivotItems(i - 1).Visible = False
sItem = .PivotItems(i)
'this takes care of the condition and copy-pasting
If pivotSht.Range("F46").Value > 0 Then
dataSht.Cells(getLastFilledRow(dataSht) + 1, 1).Value = sItem
dataSht.Cells(getLastFilledRow(dataSht), 2).Value = pivotSht.Range("F46").Value
Else: End If
Next i
End With
End With
End Sub
'gets last filled row number of the given worksheet
Public Function getLastFilledRow(sh As Worksheet) As Integer
On Error Resume Next
getLastFilledRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
source to share