Posts by gijsmo

    You are welcome.

    The code is not hidden, the macro called AddZero can be found in Module 1, this is where VBA code is stored.

    Open the spreadsheet and press the Alt + F11 key combination to see the macros:

    In order to use this in another spreadsheet, you will need to familiarise yourself with creating Modules and macros in VBA.

    Then you will be able to copy the code into any spreadsheet as long as you save the output as a macro enabled spreadsheet (typically an .xlsm file).

    I have just picked the following link as an introduction:…32-4259-9177-a71f7e626de0

    If I've understood you correctly, the attached version will recreate the SUMMARY sheet each time it is run, based on the data in the 2 OIL SALES sheets.

    I have left your original Summary sheet in the workbook (called SUMMARY-original)

    I have also left a sheet called SUMMARY-with formulas - this sheet can be deleted, it just shows the formulas used to derive the result, the macro builds these intermediate formulas when creating the final SUMMARY sheet.

    Lastly, I have left the SUMMARY sheet in the workbook but this has been created by the macro called CreateSummary.

    This macro combines the sales data, removes duplicates (based on BRAND/TYPE/MANUFACTURE columns), creates the intermediate lookup formulas and then creates the final formulas for the CASE/SURPLUS/DEFICIT columns.



    wsNew.SaveAs vFile, FileFormat:=xlCSV, Local:= True

    As per the help in the developer reference:

    True saves files against the language of Microsoft Excel (including control panel settings).

    False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English.

    I've just noticed that if you are using the code in my examples, you need to change this part in the Add sub from:

        If Not FindInRange(rRng, TTime, rFind) Then lTTime = 0


    If Not FindInRange(rRng, TTime, rFind) Then
      lTTime = 0
      lTTime = rFind.Column
    End If

    Otherwise Transit Time will never get updated even when it exists in the LEG sheet

    Some of the text has obviously been transposed, this is what that section should look like:

    If you want to eg change 93 AUD to 157 CAD (for the same OP, DP & d_type values) then just enter 157 CAD and click on the Add button - this will overwrite the 93 AUD with 157 CAD.

    If you want to clear those values then one way is to clear the Price and Currency values on the Dashboard sheet and then click the Add button. The only small issue here is that it will need a small tweak to the code to actually clear the price - the current code will set the values to 0 (zero). This tweak has been made in the version attached below.

    As for the other change, the attached version will ignore Transit Time column if it is not found.

    Dashboard TEST #4.xlsb

    Best to specify as many requirements in one go in order to minimise re-coding.

    The assumption based on the original sample was that the macro had to :

    1. Autofilter PEG sheet on OP, DP, d_type

    2. Find the number to be matched column (eg, 35037)

    3. Update the number to be matched column with price & the adjacent column with the currency

    4. Update the transit_time, expiry & effective_date columns with data from the Dashboard sheet

    All the columns were located using a Range.Find method, if one or more of the columns on the PEG sheet were missing, nothing would get updated

    So :

    1. Is Transit Time the only column that may or may not exist on the PEG sheets ?

    2. Will the column names on the other PEG sheets be the same ? eg OP, DP, d_type, etc ?

    Just remember for this to work on anything other than the PEG_3 sheet, the other PEG sheets need to have the columns called OP, DP, d_type, transit_time, expiry and effective_date

    The macro won't do any updating if these columns do not all exist

    Just a question - it would be easier to be able to copy the "template" sheets as is if there were no formulas in them as you've requested to copy without formulas. It is feasible to copy without formulas but is there some reason the formulas are in the template sheets if they are not going to be used in the workbooks to be created?