Good afternoon everyone!
I have the following code that when the difference between the sums of the pivot table equal "0", the sheet tab color changes to green.
Sub AddNewCol()
Dim WSD As Worksheet
Dim PVT As PivotTable
Dim PVTRows As Long
Dim PVTCols As Long
Dim NewRow As Long
Dim FirstCol As Long
Dim wbTo As Workbook
Dim wbFrom As Workbook
Application.ScreenUpdating = False
Set WSD = ActiveSheet
For Each PVT In WSD.PivotTables
WSD.PivotTables(PVT.Name).PivotSelect "", xlDataAndLabel, True
PVTRows = Selection.Rows.Count
PVTCols = Selection.Columns.Count
FirstCol = Selection.Column
WSD.Cells(1, PVTCols + FirstCol).EntireColumn.Clear
WSD.Cells(3, PVTCols + FirstCol) = "Devices Owed"
For NewRow = 4 To PVTRows
WSD.Cells(NewRow, PVTCols + FirstCol).FormulaR1C1 = _
"=IFERROR(GETPIVOTDATA("" Serial Rcvd"",R2C" & FirstCol & ",""Item"",RC" & FirstCol & ")-GETPIVOTDATA("" Serial Shipped"",R2C" & FirstCol & ",""Item"",RC" & FirstCol & "),"""")"
Next NewRow
WSD.Range(Cells(2, FirstCol + 1), Cells(PVTRows + 1, FirstCol + 1)).Copy
WSD.Range(Cells(2, FirstCol + PVTCols), Cells(PVTRows + 1, FirstCol + PVTCols)).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, transpose:=False
Application.CutCopyMode = False
WSD.Cells(PVTRows + 1, FirstCol + PVTCols).FormulaR1C1 = "=SUM(R4C" & FirstCol + PVTCols & ":R" & PVTRows & "C" & FirstCol + PVTCols & ")"
If WSD.Cells(PVTRows + 1, FirstCol + PVTCols).Value = 0 Then
WSD.Tab.Color = 5296274
Else
WSD.Tab.ColorIndex = xlColorIndexNone
End If
WSD.Range("A1").Select
Next PVT
Application.ScreenUpdating = True
Columns("A:D").EntireColumn.AutoFit
Call NameIt
End Sub
Specifically, this line of code...
If WSD.Cells(PVTRows + 1, FirstCol + PVTCols).Value = 0 Then
WSD.Tab.Color = 5296274
I would like to have this worksheet be moved to a folder in a directory called Closed RSA 2012. Path would be C:\Users\daniel.white\Documents\Closed RSA's 2012.
How do I change the current code to move the worksheet to this folder and save as the worksheet name?
Thanks!!!
Bookmarks