Macro to print and create a .csv..!!

[QuintonMcCloud]

Hi Guys [I POSTED THIS ON BEFORE AND WENT AWAY TO TRY AND TACKLE IT BUT FAILED ]

I have a workbook that contains material worksheets all named accordingly. I have two main objectives that I am struggling with when it comes to VBA.

Objectives:

Print- to create a button that allows the user to be able to click it and print all of the filled areas on all of the worksheets. As you will see each data is stored in columns therefore it would be beneficial either for the print macro to automatically include the new column or to just set it to include all columns of this year, however how about next year as well is the question that I am struggling with.

Export- to create a button that when clicked the user can create new .csv files all in one workbook, one worksheet all on one page. or if the easiest which I found is to create a macro for each material type however this is onerous and is a pain to then fix if they do go wrong, unless I think I am using the wrong formula.

Basically there will be a homepage and on the home page I would love to insert a button and then that button will activate the Print Macro and the other button will then activate the export feature when clicked.

N.B: The TimberGroupMeterage page has hidden cells and I would like for these cells to not appear as part of the .csv file.

Please find attached workbook.Materials -.xlsm

[Richard Buttrey]

Hi,

Before going too far with your existing layout I'd seriously consider changing your layout

If the sheets are indeed areas in which record original data then you are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

In your case you should consider a proper database with columns for
Date
Category e.g. Timber, Insulation, Sheets Sundry...etc
Ref
Material
Dimension
Units
Quantity

The Category, Material, Dimension & Units could be validation drop down pick lists

Once that's in place then you would use an Advanced Data filter macro which would react to choices you make in a criteria drop down cells and output from the database to a dedicated output range those records that you want. You would then use this output range as the source for the printed copy.

[QuintonMcCloud]

Hi Richard

The workbook you see is one with only the final reports. Basically no ammendments are made on those worksheets. They get their data from a separate workbook that is the input workbook and is linked to the one you see now. I have built a database and the data entry system is solid however it is the output that I am struggling with.

Thank you for taking your time to review my query.

Regards

[Richard Buttrey]

Hi Richard

The workbook you see is one with only the final reports. Basically no ammendments are made on those worksheets. They get their data from a separate workbook that is the input workbook and is linked to the one you see now. I have built a database and the data entry system is solid however it is the output that I am struggling with.

Thank you for taking your time to review my query.

Regards

Hi,

If there's a separate contributing workbook that holds the original data then I would suggest that that workbook was arranged in a normalised way as a pukka database so that you can take advantage of much more functionality that's available with such a database.