User extracts report from SAP, loopks up information from four other files and creates pivot tables.
After Automation:
User extracts report from SAP, clicks icon and report is automatically generated.
Design Steps:
- Add columns
- Format Heading of added columns
- Look up information from previous extract, to get status of PO
- look up information from three other files
- Contract Balance Report, to get contract available for item
- PO Spend Report, get previous PO for same item
- Master Data to get name of Buyer, description of Plant
- Create two Pivot Tables.
Screenshots
Added Columns with Formatting, Contcatenation, and VlookUp to External Files
First Pivot Table Generated
Code:
Sub openShoppingCarts()
Dim numberOfRecords As Integer
Dim MyCell As range
Dim prevFile As Workbook, newExtract As Workbook, lookUpColumns As range
For Each MyCell In ActiveSheet.UsedRange
If MyCell.Interior.Color = 65535 Then 'identify color that needs to be deleted
MyCell.EntireRow.Delete 'delete the row
End If
Next
'add the columns
Columns("A:A").Insert Shift:=xlToRight
Cells(1, 1) = "SHC2"
Cells(1, 1).Interior.Color = 65535
Cells(1, 1).Borders.LineStyle = xlContinuous 'add borders
Columns("B:B").Insert Shift:=xlToRight
Cells(1, 2) = "Status"
Columns("C:C").Insert Shift:=xlToRight
Cells(1, 3) = "Date"
Columns("D:D").Insert Shift:=xlToRight
Cells(1, 4) = "Ageing Status"
Columns("E:E").Insert Shift:=xlToRight
Cells(1, 5) = "Contract No."
Cells(1, 5).Interior.Color = 5287936
Columns("F:F").Insert Shift:=xlToRight
Cells(1, 6) = "Item"
Columns("G:G").Insert Shift:=xlToRight
Cells(1, 7) = "Supplier"
Columns("H:H").Insert Shift:=xlToRight
Cells(1, 8) = "Purchase Year"
Cells(1, 8).Interior.Color = 65535
Columns("I:I").Insert Shift:=xlToRight
Cells(1, 9) = "PO Number"
Columns("J:J").Insert Shift:=xlToRight
Cells(1, 10) = "Supplier"
Columns("K:K").Insert Shift:=xlToRight
Cells(1, 11) = "Currency"
Columns("L:L").Insert Shift:=xlToRight
Cells(1, 12) = "Price"
Columns("M:M").Insert Shift:=xlToRight
Cells(1, 13) = "Plant"
Columns("N:N").Insert Shift:=xlToRight
Cells(1, 14) = "Buyer"
numberOfRows = WorksheetFunction.CountA(Columns("O:O")) 'to get the number of records
range("A:A").NumberFormat = "@"
For i = 2 To numberOfRows Step 1
Cells(i, 1) = Cells(i, 17) & Cells(i, 15) & Cells(i, 16) ' concatenate columns Q,O &P
Next ' open files for lookup
Set newExtract = ActiveWorkbook
Set prevFile = Workbooks.Open("F:\ZSC_OPEN\Name ISC Pending SHC.xlsx")
Set lookUpColumns = prevFile.Worksheets("BCFG Pending SHC").range("A:D")
Set conFile = Workbooks.Open("F:\ZSC_OPEN\BCFG Contract Balance Monitoring.xlsx")
Set contractDetails = conFile.Worksheets("MRO$COSS Balance Monitoring").range("J:N")
Set poSpend = Workbooks.Open("F:\ZSC_OPEN\BCFG PO Details.xlsx")
Set poDetails = poSpend.Worksheets("PO Spend").range("A:F")
Set masterData = Workbooks.Open("F:\ZSC_OPEN\ZSC_OPEN-MasterData.xlsx")
Set plantDescription = masterData.Worksheets("Plant").range("A:F")
Set buyerName = masterData.Worksheets("Buyer").range("A:B")
Cells.Columns("B:B").NumberFormat = "General"
Cells.Columns("C:C").NumberFormat = "General"
Cells.Columns("D:D").NumberFormat = "General"
With newExtract.Sheets("Sheet1") 'vba vlookup
For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(i, 2) = Application.vLookUp(.Cells(i, 1).Value, lookUpColumns, 2, False)
.Cells(i, 3) = Application.vLookUp(.Cells(i, 1).Value, lookUpColumns, 3, False)
.Cells(i, 4) = Application.vLookUp(.Cells(i, 1).Value, lookUpColumns, 4, False)
.Cells(i, 17) = .Cells(i, 17) * 1 'to convert Text to Number
.Cells(i, 5) = Application.vLookUp(.Cells(i, 17).Value, contractDetails, 2, False)
.Cells(i, 6) = Application.vLookUp(.Cells(i, 17).Value, contractDetails, 5, False)
.Cells(i, 7) = Application.vLookUp(.Cells(i, 17).Value, contractDetails, 4, False)
.Cells(i, 8) = Application.vLookUp(.Cells(i, 17).Value, poDetails, 2, False)
.Cells(i, 9) = Application.vLookUp(.Cells(i, 17).Value, poDetails, 3, False)
.Cells(i, 10) = Application.vLookUp(.Cells(i, 17).Value, poDetails, 4, False)
.Cells(i, 11) = Application.vLookUp(.Cells(i, 17).Value, poDetails, 5, False)
.Cells(i, 12) = Application.vLookUp(.Cells(i, 17).Value, poDetails, 6, False)
.Cells(i, 31) = .Cells(i, 31) * 1
.Cells(i, 13) = Application.vLookUp(.Cells(i, 31).Value, plantDescription, 4, False)
.Cells(i, 27) = .Cells(i, 27) * 1
.Cells(i, 14) = Application.vLookUp(.Cells(i, 27).Value, buyerName, 2, False)
Next i
End With
'close the files
prevFile.Close savechanges:=False
conFile.Close savechanges:=False
poSpend.Close savechanges:=False
masterData.Close savechanges:=False
Cells.Select
Cells.EntireColumn.AutoFit 'adjust columns
Call createPivot 'generate pivot report
Call createPivot2 'generate pivot report
End Sub
Sub createPivot()
Dim pivotSource As range
Dim sourceSheet As Worksheet 'source sheet
Dim pivot As PivotTable ' create PivotTable
Dim pivotC As PivotCache
Dim summary As Worksheet
range("A1").Select ' select all cell with data
range(Selection, Selection.End(xlToRight)).Select
range(Selection, Selection.End(xlDown)).Select
Set pivotSource = Selection 'to be used later as pivot table source
Set sourceSheet = Worksheets("Sheet1") ' source sheet
Sheets.Add.Name = "Summary" 'name of worksheet is "Summary"
Set summary = Sheets("Summary")
Set pivotC = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=pivotSource, Version:=xlPivotTableVersion15)
Set pivot = pivotC.CreatePivotTable(TableDestination:=summary.range("A1"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion15)
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Purchase Year")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Buyer")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Quantity"), "Sum of Quantity", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Status")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Purchase Year")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Quantity")
.Caption = "Count of Quantity"
.Function = xlCount
End With
End Sub
Sub createPivot2()
Dim pivotSource As range
Dim sourceSheet As Worksheet 'source sheet
Dim pivot2 As PivotTable ' create PivotTable
Dim pivotC2 As PivotCache
Dim summary As Worksheet
Worksheets("Sheet1").Activate
Worksheets("Sheet1").range("A1").Select ' select all cell with data
range(Selection, Selection.End(xlToRight)).Select
range(Selection, Selection.End(xlDown)).Select
Set pivotSource = Selection 'to be used later as pivot table source
Set sourceSheet = Worksheets("Sheet1") ' source sheet
Set summary = Sheets("Summary")
Set pivotC2 = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=pivotSource, Version:=xlPivotTableVersion15)
Set pivot2 = pivotC2.CreatePivotTable(TableDestination:=summary.range("M1"), TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion15)
Worksheets("Summary").Activate
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Status")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Status")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Purchase Year")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Purchase Year")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Buyer")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Quantity"), "Sum of Quantity", xlSum
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Sum of Quantity")
.Caption = "Count of Quantity"
.Function = xlCount
End With
End Sub
