Custom Auto sort columns on open

[Lowa]

I've got a template that is used to export a report from our accounting system. It has information about purchase orders e.g. PO number, supplier, product etc. (a sample is attached as i was unable to attach the template)

I've created Sheet2 which shows the information from sheet1 (the is the only way it works with the accounting system).

My issue is that I'd like to automatically sort the rows in Sheet2 when the template is opened, first by supplier, then by PO number, and then by Date promised. (ascending)

I would also like to exclude or hide any rows where 'product' is blank (or 0) or at least push them to the bottom.

Can you please let me know how i can do this, keeping in mind that the file must be saved as a 1997-2003 excel template.

Regards

Peter

[leelnich]

Hi Lowa- questions:
1) Why the INDIRECT formula construction?
2) Do you wish to retain the formulas, or convert everything to constants?

[leelnich]

This Workbook_Open event procedure sorts using a temporary formula in column A. Existing formulas are retained:

Please Login or Register  to view this content.

NOTE: It would be really easy to convert these formulas to values, or even to take the data straight from sheet 1.

[Lowa]

Thanks leelnich,

I was using INDIRECT because i just copied those formulas from another template I used for another report which required that function. I'm not sure if its required for this one.

I only really need the formulas in the columns when the template is first opened (to transfer the data from sheet1 to sheet2). After that none of the information will change.

I put your formula in however the rows were not sorted when I opened the file. I think the reason for this may be because VBA code may run BEFORE the data is transfered from Sheet1 to Sheet2. would changing it to a change event fix this?

Also is there a way to hide the rows where '0' is in the Product field or push them to the bottom?

[leelnich]

OK, the new code reads the data directly from Sheet1 - no formulas required. Number Formats also changed (listed on row 2 of accompanying file).

Please Login or Register  to view this content.

BTW, this assumes data on Sheet1 always starts on row 13.

[Lowa]

I tried this VBA code and changed the formatting but there was no data in sheet2 when the file opened. I then ran the code manually and the data populated in sheet2 however some of the dates were back to front (e.g. it said 02/07/18 instead of 07/02/18) and the rows were only sorted by supplier name.

[leelnich]

1) If the code is pasted in the ThisWorkbook Object Module, and macros are enabled (Tools>Macros>Security), I don't know why it's not populating.
2) US uses month/day/year, AUS uses day/month/year. Local settings are messing us up. The dates on Sheet1 are text. If you can't figure it out on your machine, I guess you could format the Sheet2 date columns as text. They'll show correctly- but they won't SORT correctly unless you add a leading 0 to the source for months 1-9. And dates_as_text complicate any calculations based on them, which is why I tried to avoid them.
3) Apologies, the sort issue was an error on my part, an artifact of the previous code. The new version keys the correct columns.

Please Login or Register  to view this content.

[Lowa]

1) If the code is pasted in the ThisWorkbook Object Module, and macros are enabled (Tools>Macros>Security), I don't know why it's not populating.
2) US uses month/day/year, AUS uses day/month/year. Local settings are messing us up. The dates on Sheet1 are text. If you can't figure it out on your machine, I guess you could format the Sheet2 date columns as text. They'll show correctly- but they won't SORT correctly unless you add a leading 0 to the source for months 1-9. And dates_as_text complicate any calculations based on them, which is why I tried to avoid them.
3) Apologies, the sort issue was an error on my part, an artifact of the previous code. The new version keys the correct columns.

Please Login or Register  to view this content.

1) If the code is pasted in the ThisWorkbook Object Module, and macros are enabled (Tools>Macros>Security), I don't know why it's not populating.

I think the reason its not populating is because the values in sheet1 are imported from a report in our accounting system. I think the code runs when excel opens and THEN the values are imported into sheet1. So when the code runs is has nothing to sort yet. Is there a way to make the code run after the values have been imported into sheet1? Note: Sheet1 will not be touched or changed after the date is imported.

2) US uses month/day/year, AUS uses day/month/year. Local settings are messing us up. The dates on Sheet1 are text.

Is there a code i can add at the start of the above VBA code that makes all dates use my regional settings?

3) Apologies, the sort issue was an error on my part, an artifact of the previous code. The new version keys the correct columns.

Thank you, looks like it is sorted correctly now

[Lowa]

Sorry accidently double quoted you

[leelnich]

Using Aussie values for my Windows regional settings, this seemed to display dates correctly (Format columns as Short Date):

Please Login or Register  to view this content.

Peter, I don't really understand your work-flow, but I'm guessing you have formulas on Sheet1 to read the report data. The red lines are an attempt to have those calculate before the copy. If that doesn't work, perhaps you can move the code to a Worksheet_Activate event. (Worksheet_Change doesn't fire unless you actually ALTER cell contents, either manually or via code.)

[Lowa]

Thanks that formula seemed to fix the date issue

I've attached a workbook which is exactly the same as the template i'm using.

1) Basically how the process works is i'll generate a report from our accounting system (which is called MYOB). This report has the same column headings as my template (which is provided with the accounting system).
2) the report has an option to export it to the excel, which I click.
3) excel will then open with all of the data from the report entered in Sheet1.


There aren't any formulas in Sheet1 to generate the data so I think the system just relies on the column headings to know where to import data.

I think the issue is that fact that we are using a workbook_open even to run the code. this event seems to occur before the data is copied to Sheet1. is there another event we can use to activate the code at a delayed time?

[leelnich]

1) How long does it take for the workbook to open?
2) Which worksheet is selected when it opens?
3) What steps are taken after it opens? (Tells me what events will fire.)

And just for kicks- paste this in the Sheet1 Object Module, save the template, then generate a new workbook as usual. Now go to VBE>Immediate Window and see how many times the Change event fired. If data is added cell by cell, it will fire repeatedly. With no way to ID the LAST event, that's useless. However, if data is added in one go, then this event CAN serve as the code trigger.

Please Login or Register  to view this content.

[Lowa]

1) it takes a few seconds for the workbook to open
2) worksheet 2 is selected when it opens
3) after it opens i'll normally just hide the cells i don't need and then print it. no values are changed after it opens and sheet1 is never touched or opened after the workbook opens

I pasted this and when it opened the immediate window said it fired 40 times

[Lowa]

would it work if we made the code run when, for examples cell D2 on sheet1 is changed, as this cell would only change once?

[leelnich]

We can't run the code until all the data has been added to sheet 1, but we don't know which cell is changed last. Code is triggered by events, not by cessation of events.

[Lowa]

is it possible to attach the code to a button so i can just press the button and the code will run?

[leelnich]

Yes, of course. You could include the hiding and printing, too, if that is repeatable. Can you record a macro of your print process and post it?
1) Are you just hiding empty rows?
2) Are there any Print settings that get changed? For example, the worksheet layout appears to favor printing in Landscape orientation.

[Lowa]

I ended up being able to make it activate when Sheet2 is activated (get it to open on sheet1) and then went from there to only show rows with values in column E and to colour coordinate them. If anyone's interested the code i used to make everything work is:

Please Login or Register  to view this content.

thanks for you help leelnich

[leelnich]

Nice! You could consolidate the loops:

Please Login or Register  to view this content.

I removed the dictionary object, as it wasn't actually used. If you need to skip duplicates, I could fix it.
Also, you could probably eliminate the section in red, as these are normally default settings.

PS. Thanks for the rep, glad I could help - Lee