Option Explicit
Sub Main_FinExport()
Stop
If bProduction = True Then On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim wbkSource As Workbook
Set wbkSource = Workbooks.Open(wFinExport.Range("FinMasterPath"), False, True)
Dim pvtSource As PivotTable
Set pvtSource = wbkSource.Sheets("Fin").PivotTables(1)
Dim wbkTarget As Workbook
Set wbkTarget = Workbooks.Add
Dim wksTarget As Worksheet
Set wksTarget = wbkTarget.Sheets.Add
wksTarget.Name = "Fin export"
Dim wksTargetOriginal As Worksheet
Set wksTargetOriginal = wbkTarget.Sheets.Add
wksTargetOriginal.Name = "Fin export original"
Dim sCurrentMonth As String
sCurrentMonth = GetCurrentMonth(wbkSource)
Call CopyValues(pvtSource, wksTarget, wksTargetOriginal)
Call HighlightCurrentMonth(sCurrentMonth, wksTarget, wksTargetOriginal)
Call WriteFormulas(sCurrentMonth, wksTarget)
Call HighlightChanges(wksTarget, wksTargetOriginal)
Call FreezePanes(wksTarget)
Call DeleteDefaultSheetsAndActivateFirst(wbkTarget)
wksTargetOriginal.Visible = xlSheetHidden
Call LogMacro(wbkSource.Sheets("MacroLog"), Now, "Export Fin", "")
'Save and close target file
Dim sFileName As String
sFileName = wFinExport.Range("ExportFolder").Value & "Fin playground " & Replace(Now, ":", "-")
Application.DisplayAlerts = False
wbkTarget.Close SaveChanges:=True, Filename:=sFileName
Application.DisplayAlerts = True
'Close Fin file
wbkSource.Close SaveChanges:=False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox ("Export of Fin report has been completed.")
Exit Sub
ErrHandler:
MsgBox ("An error happened. Do not save the files.")
End Sub
Sub CopyValues(pvtSource As PivotTable, wksTarget As Worksheet, wksTargetOriginal As Worksheet)
' One row at a time to not run out of memory
Dim r As Range
wksTarget.Activate
For Each r In pvtSource.TableRange1.Rows
r.Copy wksTarget.Cells(r.Row - pvtSource.TableRange1.Row + 1, 1)
Next
wksTargetOriginal.Activate
For Each r In pvtSource.TableRange1.Rows
r.Copy wksTargetOriginal.Cells(r.Row - pvtSource.TableRange1.Row + 1, 1)
Next
End Sub
Function GetCurrentMonth(wbkSource As Workbook) As String
Dim caches As Excel.SlicerCaches
Set caches = wbkSource.SlicerCaches
Dim cache As Excel.SlicerCache
Set cache = wbkSource.SlicerCaches("Slicer_CurrentMonthName")
Dim sCurrentMonthPowerString As String
sCurrentMonthPowerString = cache.VisibleSlicerItemsList(1)
Dim sCurrentMonth As String
sCurrentMonth = GetPivotItemFromPowerPivot(sCurrentMonthPowerString)
GetCurrentMonth = sCurrentMonth
End Function
Sub HighlightCurrentMonth(sCurrentMonth As String, wksTarget As Worksheet, wksTargetOriginal As Worksheet)
Dim lCurrentMonthCol As Long
lCurrentMonthCol = wksTarget.UsedRange.Find(What:=sCurrentMonth, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Column
wksTarget.Columns(lCurrentMonthCol).Interior.Color = RGB(122, 122, 122)
Dim lMonthNameRow As Long
lMonthNameRow = wksTarget.UsedRange.Find(What:="MonthName", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Row
wksTarget.Cells(lMonthNameRow, lCurrentMonthCol) = "Current Month"
wksTarget.Cells(lMonthNameRow, LastColumn(wksTarget)) = "Last Forecast Month"
wksTargetOriginal.Cells(lMonthNameRow, lCurrentMonthCol) = "Current Month"
wksTargetOriginal.Cells(lMonthNameRow, LastColumn(wksTargetOriginal)) = "Last Forecast Month"
End Sub
Sub WriteFormulas(sCurrentMonth As String, wks As Worksheet)
Dim lCurrentMonthCol As Long
lCurrentMonthCol = wks.UsedRange.Find(What:=sCurrentMonth, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Column
Dim lFirstMonthCol As Long
lFirstMonthCol = wks.UsedRange.Find(What:="MonthName", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Column
' There must be historical month for the inventory formula to work
If lCurrentMonthCol = lFirstMonthCol Then
MsgBox ("Please filter to show at least one actual month in the Fin power pivot report and then run the Fin export again. This is required for the inventory formulas to work.")
End If
Dim lCustomerCol As Long
lCustomerCol = wks.UsedRange.Find(What:="Customer", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Column
Dim lLastCol As Long
lLastCol = LastColumn(wks)
Dim lLastRow As Long
lLastRow = LastRow(wks)
Dim c As Long
Dim r As Long
Dim lLastPurchaseRow As Long
Dim lLastSalesRow As Long
For r = 1 To lLastRow
' Save row number
If wks.Cells(r, lCustomerCol) = "Purchase" Then
lLastPurchaseRow = r
ElseIf wks.Cells(r, lCustomerCol) = "Sales" Then
lLastSalesRow = r
End If
'Write formula
If wks.Cells(r, lCustomerCol) = "Sales" Then
'Test if there are any sales company rows
If lLastPurchaseRow < r - 1 Then
wks.Range(wks.Cells(r, lCurrentMonthCol), wks.Cells(r, lLastCol)).Formula = "=SUM(" & wks.Cells(lLastPurchaseRow + 1, lCurrentMonthCol).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ":" & wks.Cells(r - 1, lCurrentMonthCol).Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")"
Else
wks.Range(wks.Cells(r, lCurrentMonthCol), wks.Cells(r, lLastCol)).Formula = "=SUM(0)"
End If
ElseIf wks.Cells(r, lCustomerCol) = "Inventory" Then
wks.Range(wks.Cells(r, lCurrentMonthCol), wks.Cells(r, lLastCol)).Formula = "=" & wks.Cells(r, lCurrentMonthCol - 1).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "+" & wks.Cells(lLastPurchaseRow, lCurrentMonthCol).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "-" & wks.Cells(lLastSalesRow, lCurrentMonthCol).Address(RowAbsolute:=False, ColumnAbsolute:=False)
End If
Next
End Sub
Sub HighlightChanges(wksTarget As Worksheet, wksOriginal As Worksheet)
Dim lFirstRow As Long
Dim lLastRow As Long
Dim lFirstCol As Long
Dim lLastCol As Long
Dim r As Range
Dim vTempValue As Variant
lFirstRow = 1
lLastRow = LastRow(wksTarget)
lFirstCol = 1
lLastCol = LastColumn(wksTarget)
Set r = wksTarget.Range(wksTarget.Cells(lFirstRow, lFirstCol), wksTarget.Cells(lLastRow, lLastCol))
With r
r.Parent.Parent.Activate 'Se comment on row below
r.Parent.Activate 'Se comment on row below
.Select 'Necissary when working with formatconditions in VBA and using relative references because of bug in Excel 2007 and earlier
.FormatConditions.Delete
vTempValue = wksTarget.Cells(1, 1).Value 'Put formula into cell because of localization bug in conditional formatting formula1
wksTarget.Cells(1, 1).Formula = "=" & wksTarget.Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False) & "<>" & "'" & wksOriginal.Name & "'!" & wksOriginal.Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)
.FormatConditions.Add Type:=xlExpression, Formula1:=wksTarget.Cells(1, 1).FormulaLocal
.FormatConditions(.FormatConditions.Count).Interior.Color = RGB(255, 165, 0)
wksTarget.Cells(1, 1) = vTempValue 'restore
End With
End Sub
Sub FreezePanes(wksTarget As Worksheet)
Dim lHeaderCol As Long
lHeaderCol = wksTarget.UsedRange.Find(What:="Customer", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Column
Dim lHeaderRow As Long
lHeaderRow = wksTarget.UsedRange.Find(What:="Customer", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False).Row
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
ActiveWindow.FreezePanes = False
wksTarget.Parent.Activate
wksTarget.Cells(lHeaderRow + 1, lHeaderCol + 1).Activate
ActiveWindow.FreezePanes = True
End Sub
Bookmarks