Time Saving Techniques?

[Xelpme]

I wonder if anyone can help me, I think part of the solution involves using active cell.

I import lots of data into a spreadsheet then have to manually format it which takes hours, I want code to automate it.

Example: I have data such as:

No-----Name-----Date---Price----Cost
10-----Dino------etc-----1--------2
10-----Dino------etc-----3-------5
12-----Bino------etc-----4--------6
13-----Rino------etc------5-------5
13-----Rino-----etc-------6--------6


I want it looking like this:

No-----Name-----Date---Price----Cost
10-----Dino------etc-----1--------2
10-----Dino------etc-----3-------5
shaded cells to here ishh 4-------7

No-----Name-----Date---Price----Cost
12-----Bino------etc-----4--------6
shaded cells to here ishh 4---------6

No-----Name-----Date---Price----Cost
13-----Rino------etc------5-------5
13-----Rino-----etc-------6--------6
shaded cells to here ishh 11-------11

The difficulty is due to the fact that the number in No column varies, for example there may be 2 rows of 10 or 4 rows of 10. What is the quickest way to format it all too, so have a border and font size etc all the same?

At present I have to manually insert blank rows between the different numbers in the No column, copy column headings, then manually sum up Price and Cost, then insert borders. But for hundreds of records.

Any ideas?

I found this code to insert 3 blank rows manually for me when the numbers in No column are different:

Please Login or Register  to view this content.

Thanks for any suggestions inc web sites or books, if this is possible in a macro or VBA.

[mudraker]

Xelpme

Welcome to excel forum

Please read forum rules - see blue link below

Your message broke the rule about wrapping VBA code - see red link below.
I have fixed you message this time.

Now to your problem

Try this macro

Note:- I have not added any shading as I did not know what you wanted shaded.

Please Login or Register  to view this content.

[Leith Ross]

Hello Xelpme,

This macro sorts the data first to group all the same numbers together. It creates a table for each set of numbers and their totals (Price and Cost). The totals are in bold, and each table is seperated by 2 blank lines. See the attachment for how the table is formatted. After you insert a Standard VBA Module into your workbook, copy and paste the macro code into it. You can then assign it to a command button or run it using ALT+F8. The worksheet names in the macro Sheet1 and Sheet2. Sheet1 has the data, and Sheet2 is were the tables are created. Things can be changed to the names you are using. It looks long because of all the formatting code, but it works quite fast.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Xelpme]

mudraker,

Sorry about breaking a forum rule regarding formatting of code.

Thank you for your coding expertise. I tried your code and it works well, I have been playing around with it trying to tinker it to my design but cannot work out how to do so.

All I want to know regards formatting so:
1. Each table is separated by a blank row.
2. Each table is surrounded by the "All Borders" option.
3. At the bottom row of each table, before the total columns, there are 3blank cells, I just want to fill this with a colour such as grey, I also need this for another blank cell on the same bottom row of each table for a column after the cost.
4. Each heading in each table has a grey background with white bold font for any font but default.

Also, there are times when some numbers in the No column contain an additional letter and need to be grouped together with this same number. E.g. 10, 10f, 10g, all should belong to the same separate table.

Finally a personal question. How long have you been working with Excel to gain the expertise to write this code? I would love to write it myself but haven't the time.

Thanks again for your knowledge.





Leith Ross,

I tried your code but nothing happened at all. Thank you for your time though.

[Leith Ross]

Hello Xelpme,

If you post your workbook I'll install the macro for you. If you can't post the workbook, due to your location or confidentiality, post a larger sample of data that I can use. I will create a workbook from that with the macro it so you can see how it runs.

Sincerely,
Leith Ross

[mudraker]

Have a look at the attached book

Run MainMacro

Been writing macros early 1990's

[Xelpme]

Sorry about the long delay, I have been busy during the week annd didn't have time to respond.


mudraker

I tried your latest macro and it was great except for a little formatting. Thank you.


Leith Ross

I have uploaded a an example spreadsheet with 3 scenarios, so 3 macros required.

1. Create table for each code including number and letter belonging to same table, so 11 and 11a belong to same table.
2. Create table for each unique code, so 11 and 11a would have their own table.
3. Create table for each unique code and sort by date with each month having a separate table.

It should be easy to understand once you see the spreadsheet.


Could anyone recommend a good excel book or web site to help me understand the code from mudraker and Mr Leith Ross, so I can add to it and make changes later on if necessary?

Thanks for your expertise mudraker and Leith Ross.

[mudraker]

Xelpme

I have added cooments to my code to help explain what it is doing
I hope you can follow & understand my code & comments

I have made a couple of small changes to the code to improve its efficiency - still works the same.
And also removed a module sheet that was left over from when I was writing the code.

Do you need any help with the formatting that is not as required?


In our Excel Miscellaneous forum there is a sticky thread that has links to sites that will help explain differnet code functions etc.

[Xelpme]

mudraker

Thanks for updating your code and including comments, it has been helpful and allowed me to understand and update it to the format I require. However I am unable to convert the prices to currency, only the total has been formatted to currency and the bold is not being applied to the total for some reason.

Also I need to format the dates like 1-April-07 ,and, if you look at my previous post and uploaded spreadsheet, I need different things for different scenarios.

Thanks for the helpful commenting.

Please look at the spreadsheet after my formatting, I didn't update the comments though, will do it when I've finished.

[Leith Ross]

Hello Xelpme,

The attachedd workbook has been updated to provide you with most of what you asked for. Excel can't sort the codes 11, 11a, 11b, etc. as a group. I am writing a sorting algorithm to deal with this issue. The other part I haven't added yet is the grand total lines. The worksheet now has a button that displays a UserForm for you to select how you want the data sorted. Have a look and let me know what you think.

Sincerely,
Leith Ross

[Leith Ross]

Hello Xlepme,

The attached workbook contains a code correction for Book9(a), and I havve added the grand total line. I am still working on the sorting algorithm. 2 out the 3 formats are functioning completely.

Sincerely,
Leith Ross