Skip to content

Automate your work – easily split large price lists!

When you work on ERP price lists, you end up with thousands of lines of price list – SKU combinations. When sending these files to review, you need to filter and save as tens of files. How to automate this?

If your company is not using a price-management suite, the annual price review is a hell of a manual work to do. I know it, I have done if 5+ times already. You have to do, correct, and re-do many reports tens of times. It really wears you out.

One of the most time-taking tasks of the annual-price review, I find, is to split the long price agreement file into one-file-per-reviewer, send them to reviewers, and import their feedback into the original file.

For example, if you are,

  • Working on Oracle,
  • 100 actively-running SKUs in price lists,
  • 100 customers with price agreements,
  • 10 regional managers;

then you need to split a 10,000 lines excel file into 10 files, add relevant data (like gross margin, suggested price increase etc.), and send it to 10 regional managers for review.

If you do a single mistake in the file and notice this after you send it, here you go again. Filter – save as – close – filter – save as – close – filter – save as – close– filter – save as – close…

Or, you can save time with this VBA code:

Step 1: Structure your excel file

  • Create a sheet named “PA_List”, and create a table as below:

(thanks to behindthename.com for random names)

  • Create a sheet named “PA_Lines” and copy your large price list database into it as follows:

Now hit Alt + F11 to launch the macro editor, right click on your file and select Insert -> Module. Then copy this code into your module:

Sub Split_Files()

' First, define variable that will be used in the macro
Dim PA_No As Double
Dim New_WB, Master_WB As Workbook
Dim PA_List, PA_Details As Worksheet
Dim SaveFileName As String
Dim Managers() As String
Dim Manager As Variant
Dim SearchString As String
Dim SearchArray() As String

'Assign all the managers to an array.
'We will loop through this array to save each file.
Managers = Split("Michael,Christèle,Arnoud,Linus", ",")
Set Master_WB = ActiveWorkbook
Set PA_List = ActiveWorkbook.Sheets("PA_List")
Set PA_Details = ActiveWorkbook.Sheets("PA_Lines")
For Each Manager In Managers
    'Create the new workbook
    Set New_WB = Workbooks.Add
    New_WB.Worksheets.Add
    
    ' Filter the PA_List based on manager, and copy this table
    Master_WB.Activate
    PA_List.Activate
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$E$190").AutoFilter Field:=3, Criteria1:=Manager
    Range("A1:E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    'Paste the copied PA list to the new split file
    New_WB.Activate
    New_WB.Sheets(1).Activate
    Range("A1").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("A1").Select
    
    'Rename the sheets of the split file
    New_WB.Sheets(1).Name = "PA_Summary"
    New_WB.Sheets(2).Name = "PA_Details"
    New_WB.Sheets(2).Activate
    
    'Initiate / clear the searchstring before each loop
    SearchString = ""
    
    'Add each PA to be reported in an array, which will be used as a filter in the next step
    For PA_No = 2 To 190
        If PA_List.Cells(PA_No, 5) = "Yes" And PA_List.Cells(PA_No, 3) = Manager Then SearchString = SearchString & "," & PA_List.Cells(PA_No, 1)
    Next PA_No
    
    SearchString = Mid(SearchString, 2, Len(SearchString) - 1)
    Erase SearchArray
    ReDim SearchArray(70)
    SearchArray = Split(SearchString, ",")
   
    'Set the filter on the PA_Lines worksheet, based on the selected PAs at the previous step.
    Master_WB.Activate
    PA_Details.Activate
    ActiveSheet.Range("A1").AutoFilter
    ActiveSheet.Range("$A$1:$BK$18272").AutoFilter Field:=1, Criteria1:=SearchArray, Operator:=xlFilterValues
    
    'Copy the filtered results
    Range("A1:G1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    'Paste the filtered results into the new worksheet
    New_WB.Activate
    New_WB.Sheets(2).Activate
    Range("A1").Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                
    'Change the output path after you create a new folder
    SaveFileName = "C:\Users\YourUserNAme\Documents\Review_Files\" & Manager & "_PriceAgreement_Review_2023"
    
    'Save the split workbook as a new file, close, and go to the next reviewer.
    New_WB.SaveAs Filename:=SaveFileName
    New_WB.Close
             
Next Manager

End Sub

Now you can run this code, and you will see 4 files, containing all the lines to be reviewed by each manager. They can run the review, send you back the file, and you can import it.

BONUS: I also have a code to bulk-import the reviewed files into a single master price agreement, and log changes! If you are interested, write me on the contact form and I will send you that code as well!

Leave a Reply

Your email address will not be published. Required fields are marked *