You are welcome, my general approach is to avoid loops - use code like you would approach the problem manually as native Excel functions like autofilter are pretty efficient in VBA.
Good luck, never too late to learn
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:
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.
I've added an ExportToCSV macro to the attached example.
This uses a prompt for the month number, expected input is 1 to 12 (default = current month number). It assumes the current year is to be used.
This locates the data to be exported using an autofilter method which should be more efficient than looping.
You are welcome, it has saved me a few times!
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.
Thank you, you are welcome!
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:
Otherwise Transit Time will never get updated even when it exists in the LEG sheet
Based on a quick look at those tabs, it will be failing because there is no column called cargo_type on LEG4 or LEG2
Some of the text has obviously been transposed, this is what that section should look like:Code
With wTarg 'assume the header is row 1 Set rRng = .Rows(1) 'try finding the matching number first If Not FindInRange(rRng, sNum, rFind) Then GoTo QuickExit lNum = rFind.Column If Not FindInRange(rRng, OP, rFind) Then GoTo QuickExit lOP = rFind.Column If Not FindInRange(rRng, DP, rFind) Then GoTo QuickExit lDP = rFind.Column If Not FindInRange(rRng, DType, rFind) Then GoTo QuickExit lDType = rFind.Column If Not FindInRange(rRng, Exp, rFind) Then GoTo QuickExit lExp = rFind.Column If Not FindInRange(rRng, EDate, rFind) Then GoTo QuickExit lEDate = rFind.Column 'transit time may or may not exist If Not FindInRange(rRng, TTime, rFind) Then lTTime = 0 End With
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.
If I read this right, you just need to enter 157 as Price and CAD as Currency on the Dashboard sheet and then click on the Add button again to change the values.
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
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?
Yeh, it's a simple change to allow the PEG sheet to be selected from the Dashboard.
Updated version is attached.
My version is using autofilter which is relatively quick and efficient (and relatively easy to follow the code too I think).
There are lots of comments in the code so hopefully this is relatively clear.