Visual Basic and macros to ease up your work!

It has been a while that I have been a part of FP&A process and I have been exploring ways to ease up a bulk of the work we do. While I was a little apprehensive initially of VBA and coding as I come from finance background, I have to say this has saved us a lot of time and provided us good ample opportunities to do other stuff. Besides, we have this feature of macros in VBA which automatically helps updates the code. All you then need is the ability to interpret it which thanks to google and the internet has magnified our ability to understand and learn it much more quicker.

Herewith, I am providing you a mock version of the code I had run to help us save a good amount of time. The case that I dealt with was we had a master file in a table format which had a list of all the expenses that were incurred in a particular profit center in SAP. These had to be segregated on the basis of cost centers and pivot tables created and sent to individual Stake holders to analyse the data and interpret it. I am sure a lot of professionals deal with such stuff wherein they have a master data which needs to segregated based by providing a particular filter and pivots created for this file and sent across to multiple people for their analysis. Instead of doing it manually, you could always use VBA to automate this process and send them across. So here it is for you -

Option Explicit

Sub Split_data_pivot

Application.screenupdating = False

Dim msg as worksheet

Set msg = ThisWorkbook.sheets("Sheet1")(This is the name of the sheet you want to split. It may need to tweaked based on the name. :))

Dim nam as worksheet

Set nam as ThisWorkbook.sheets("Sheet2")(This is the name of the sheet which would have the path the files would be saved into . It may need to tweaked based on the name and please ensure to have the entire path in cell H6 of that sheet. :))

Dim abc as workbook

Dim def as worksheet

nam.Range ("A:A").Clear

msg.autofiltermode = False

msg.range("AN:AN").copy nam.range("A1") (Please note that AN is the column that needs to be filtered. It may have to be changed.)

nam.range("A:A").removeduplicates 1, xlYes

Dim i As Integer

For i = 2 To Application.CountA(nam.Range("A:A"))

msg.UsedRange.AutoFilter 40, setting_Sh.Range("A" & i).Value (This should be tweaked according to the column no. of your data)

Selection.Copy

Sheets.Add

Set abc = Workbooks.Add

Set def = abc.Sheets(1)

msg.UsedRange.SpecialCells(xlCellTypeVisible).Copy def.Range("A1")

def.UsedRange.EntireColumn.ColumnWidth = 15

def.Select

ActiveSheet.Paste

Dim PTcache As PivotCache

Dim PT As PivotTable

' Create the cache

Set PTcache = ActiveWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, _

SourceData:=Range("A1").CurrentRegion)

'Add a new sheet for the pivot table

Worksheets.Add

' Create the pivot table

Set PT = ActiveSheet.PivotTables.Add( _

PivotCache:=PTcache, _

TableDestination:=Range("A3"))

'Specify the fields

With PT

.PivotFields("Name").Orientation = xlRowField

.PivotFields("Description").Orientation = xlLabelOnly

.PivotFields("Period").Orientation = xlColumnField

.PivotFields("Amount").Orientation = xlDataField

.RowAxisLayout xlTabularRow

.TableStyle2 = "PivotStyleMedium7"

Range("C5").Select

(Please note that the items highlighted in bold are the specific columns or rows you would like to be a part of your pivot table. Please tweak them accordingly)

End With

Next i

nam.Range("A:A").Clear

MsgBox "Done"

End Sub