Working with the Module Line Items Export

PrevContributor
PrevContributor
in Integrations and extensibility

Whenever I need to dig into the model by line item, I always export the module line items (Settings -> Modules -> Line Items tab) and quickly filter it in excel.  The only problem is, when you filter down onto a subset, you lose the ability to see what module the line items apply to.  For those of you that use VBA in Excel, this code will insert a new column to the left, and then copy the module names down the new column.  This will give you all the information you need when working with the model line items. Sub Anaplan_Line_Item_Export() Application.ScreenUpdating = False 'Copy Module Names Loop     ActiveSheet.Range("a1").Select          'Inserts New Column for Module Names:         Columns("A:A").Select             Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove          'Copy Module Name Loop         ActiveSheet.Range("A2").Select         Do Until ActiveCell.Offset(0, 1).Value = ""             If ActiveCell.Offset(0, 1).Font.ColorIndex = 2 Then                 ModuleName = ActiveCell.Offset(0, 1).Value                 AppliesTo = ActiveCell.Offset(0, 6).Value                          End If                      ActiveCell.Value = ModuleName                              'Cleans Applies-To                 If ActiveCell.Offset(0, 6).Value = "-" Then                     ActiveCell.Offset(0, 6).Value = AppliesTo                 End If                  'Cleans Formula's "'"                 If ActiveCell.Offset(0, 2).Value <> "" Then                     ActiveCell.Offset(0, 2).Formula = "'" & ActiveCell.Offset(0, 2).Formula                 End If                                                        ActiveCell.Offset(1, 0).Select              Loop          'Apply Formatting to New Data         ActiveSheet.Range("b2").Select         Selection.Copy             lastrow = Range("A" & Rows.Count).End(xlUp).Row                 Range("A2:A" & lastrow).Select                     Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False             Columns("A:A").EntireColumn.AutoFit             Columns("B:B").EntireColumn.AutoFit             Columns("C:C").Select                 With Selection                     .HorizontalAlignment = xlGeneral                     .WrapText = False                     .Orientation = 0                     .AddIndent = False                     .IndentLevel = 0                     .ShrinkToFit = False                     .ReadingOrder = xlContext                     .MergeCells = False                 End With                 Selection.ColumnWidth = 100     Range("a1").Select Application.CutCopyMode = False MSG1 = MsgBox("Reformat Complete!" & vbLf & vbLf & "Sort by Cell Count?", vbYesNo, "Ryan Whalen's Anaplan Auto-Reformat") If MSG1 = vbYes Then Range("A1:S" & lastrow).Select     Range("B5").Activate     ActiveWorkbook.Worksheets("Sheet 1").Sort.SortFields.Clear     ActiveWorkbook.Worksheets("Sheet 1").Sort.SortFields.Add Key:=Range( _         "R2:R" & lastrow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _         xlSortNormal     With ActiveWorkbook.Worksheets("Sheet 1").Sort         .SetRange Range("A1:S" & lastrow)         .Header = xlYes         .MatchCase = False         .Orientation = xlTopToBottom         .SortMethod = xlPinYin         .Apply     End With      Else   End If     Range("A1:S" & lastrow).Select     Selection.AutoFilter     ActiveWindow.Zoom = 80     Range("A1").Select End Sub Regards, Ryan

Tagged:

Answers

Categories