Macro To Print Specific Sheets In Excel

In Excel 27 and earlier the basic approach would be to trap the print event with a Workbook_BeforePrint macro hide the unused pages allow the printout to occur then unhide the pages. Here is some sample code 'This code must be installed in the ThisWorkbook code pane. It won't work at all if installed anywhere else!nPrivate Sub Workbook_BeforePrint(Cancel As Boolean)nCall Now() UnhideUnusedPagesnEnd Sub n'This code should be installed in a regular module sheetnSub HideUnusedPages(Optional b As Boolean) 'Parameter b not used but takes macro off list in Macro Selector dialognDim cel As RangenWith Worksheets(Requisition) 'The worksheet where content needs to be hidden before printingn If .Range(C3) < abc Then .Range(A3Z25). = Truen If .Range(C26) < abc Then .Range(A26Z53). = Truen If .Range(C54) < abc Then .Range(A54Z78). = Truen If .Range(C79) < abc Then .Range(A79Z13). = TruenEnd WithnEnd Sub Sub UnhideUnusedPages(Optional b As Boolean)nWith Worksheets(Requisition)n . = FalsenEnd WithnEnd Sub horizontal-rule In Excel 21 and later the Workbook_BeforePrint event doesn't run before displaying the dialog showing what will print and what does not. I believe it will still run once you click the Print button but do not have a printer available to me to test it. Should that approach not work you will need to provide a button on your worksheet to print the worksheet out. That button will use code like the following 'This code should be installed in a regular module sheetnSub PrintUsedPages()nCall HideUnusedPagesnWorksheets(Requisition).PrintOutnCall UnhideUnusedPagesnEnd Sub Sub HideUnusedPages(Optional b As Boolean) 'Parameter b not used but takes macro off list in Macro Selector dialognDim cel As RangenWith Worksheets(Requisition) 'The worksheet where content needs to be hidden before printingn If .Range(C3) < abc Then .Range(A3Z25). = Truen If .Range(C26) < abc Then .Range(A26Z53). = Truen If .Range(C54) < abc Then .Range(A54Z78). = Truen If .Range(C79) < abc Then .Range(A79Z13). = TruenEnd WithnEnd Sub Sub UnhideUnusedPages(Optional b As Boolean)nWith Worksheets(Requisition)n . = FalsenEnd WithnEnd Sub

Disclaimer I built Holistics ( italic Holistics Data Software italic ) a BI product. italic It depends on a lot of factors. But generally you can think about it in 3 verticals Use Cases What are the key reports youre looking at? Do you need high-level pretty visualizations or detailed-level data extractions or both? Are your report logicsplex or simple aggregation? What about features daily email reports email alert dashboards self-serve pivot table? Creation Process Who will be the ones preparing the reports? Are they more technical (i.e. can write Excel macros queries) or non-technical (i.e. prefer drag and drop interface)? ordered-list There are lots of BI solutions out there on the market that will fit you best based on yourbined answers to the above points. And it hard for me to rmend the best without knowing your specific answers. But based on number 2 and 3 above ( Data Stores and Creation Process ) I like to differentiate 2 camps of BI softwares for you. 1- Heavyweight Tableau QlikView Sisense Yellowfin BI IBM Cognos etc. Heavyweight italic They typically load all your data into their proprietary data storage format. This is easy to understand because when they started data warehouse is still expensive (there no Amazon Redshift or BigQuery) These products have been around for a while (started before 21 with Qlikview started wayyyy back in 1993) They cater towards the non-technical business analysts (prefer drag and drop interface); due to this moreplex-logic reports are difficult (sometimes impossible) to produce. Theyre mostly self-hosted on your servers (you provided your own server) They have goodplex visualizations They are generally expensive pared to the second camp below) 2- Lightweight Redash Metabase Chartio etc. Lightweight italic Most of these connect directly to your application database to extract data and visualize. These are more recent players on the market (post 21) where they focus on the BI part leaving the data processing to data-warehouses (relational databases like Postgres Redshift BigQuery etc) Most of these cater to the technical analysts (prefer to write SQL queries for flexibility) with simple drag and drop interface that translates back to SQL They have decent visualizations; they are mostly cloud-based. They are more affordable than the first camp. Within each camp different product will have their own pros and cons so I suggest you look at each and make your own assessment. (where Im the product creator and cofounder) has a pretty good set of features that you could consider Easy & Flexible Extremely easy to start no training required (reports are based on SQL query language; we even extended SQL to make it easier!) Great at detailed-level data reports; most other BI tools focus on high-level summarized data we provide a superior interface for end analysts to create detailed operational data reports. Push data to your users with scheduled emails and email alerts Record-level Permission Besides the usual user permission management we even allow you to permission control at the record level. italic (e.g. create a single Sales report that users in US only see US data and Canada users only see Canada data). Data Infrastructure We help pull data from different sources (application databases and spreadsheets) and import them into your centralized data warehouse. italic If you maintain lots of Excel Sheets we also help you sync them into your DW. Data Security We don store your data Holistics works directly with your database Other highlight features Pivot Table Cohort Analysis Embedded Dashboards Affordable italic pricing starts from just $5 for small teams Hope this helps give you a few pointers to where to start.

My entire job rotates around get it done yesterday reporting. Every Database I use Oracle SAP SQL and Software I use for reporting such as Tabelau BOBj. All end up Ad-HOC reporting and go into an excel sheet. Every report exported is in CSV Text or Xls format. The very first and most import tricks I learned in Excel were INDEX VLOOKUP Naming Tables (Cmon Name your Ranges it cleans your formulas and works better ) and Tabulated Form Pivot tables. Excel will never be replaced its used for tracking shipments inventory spend KPI; mini-Databases online data entry has Macro Capability the list goes on. You can run an entire business with Excel and Outlook. Trust me I work for a multi-Billion dollar fortune 1pany and they Excel for EVERYTHING even if they have a tool specifically for that task. Resistance to change will always be there. They want their reports in excel. They want their data in excel and email. So as far as professional benefit? The most important skill I have is advanced knowledge of Excel. Next most important software is my web browser. You might think its Outlook but you would be wrong because you can print an excel sheet.

In 1994 I was asked to write some software for a client. They had licensed some technology from my employer and needed a Windows program to size the equipment and predict its performance. I hadn written any software at all for 15 years and then it was spaghetti code in Fortran IV for use on an IBM mainframe. So of course I said yes and offered a $3 firm price before they had written any specs for the project. I went to theputer store and asked them which language I should write the code in. The clerk suggested C so I asked to see some code written in that language. Could not make heads or tails out of the syntaxand immediately asked for a different suggestion. The clerk then showed me Visual Basic 3 which looked similar enough to Fortran IV that I bought thepiler and took it to work the next day to start reading up on the language. In those days Visual Basic came with a magazine subscription. I found most of the articles to be impenetrable. Im a mechanical engineer not aputer science major. I had no idea what those client servers were that most of the articles talked about. But one editorial resonated with me. It talked about the evils of spaghetti code and how the use of statement numbers caused it. It suggested the use of logically structured code as an alternative. And it said that the newly released Excel 5 had a new macro language that was based on Visual Basic. At this point a big lightbulb turned on in my mind. I had previously suffered through the problems of specifying the formats for punched card input and line-printer output in FORTRAN IV. Using Excel for inputs and outputs and this Visual Basic stuff for calculations seemed like a great solution to my problem. And since I already had Excel 5 installed on myputer let dive right in. About this point I received 15 pages of specifications from the client. The specs talked all about the language to be usedpilation and ing version control more of that client server nonsense documentation requirements etc. Not a single word mentioned the inputs expected outputs or user interface. Because I didn understand the specs and because they seemed irrelevant I decided to ignore them. I then spent the next 2 months breaking the problem down into small pieces. I coded each piece as a user-defined function in VBA tested it against sample problems in my books and made corrections as required. Nobody else where I worked knew how to program so I waspletely on my own. I wrote notes all over the Programmer Ge and Language Reference books that Microsoft supplied. It was then time to put all the pieces together to solve the sizing and performance problems. By this point I realized that the problem was essentially one of counting snowflakes. The client had a machine (using the licensed technology) that made those snowflakes a big tank to accumulate them and an end user who wanted to melt those snowflakes at particular times during the day. Armed with this mental model I then proceeded to write the longer pieces of code to call my building blocks and start counting those snowflakes. It took another couple of months to code my mental model. Along the way I realized that it was way too slow. I needed at least 1X improvement in speed and got it through the use of array transfer between VBA and the Excel worksheet and optimizing the algorithms used for the nested trial and error solutions to interrelated problems. I then printed out a sample input sheet and some results from my program and mailed it to the client. Got some feedback addressed it and scheduled a meeting with the client to show them the program. Believe you me they were not happy to learn that the long-awaited program was an Excel spreadsheet. But I sold them on the fact that the code was easy to maintainthey had a copy of the source code and it was liberallymented and readable by engineers. Since the last program theymissioned was written in C andpiled they were quite sensitive to the issue of not receiving a copy of the source code. And since all the calcs were done behind the scene by about 5 pages of VBA the user (a salesman) was hard-pressed to mess things up. They gritted their teeth acquiesced to the fact that I had followed absolutely none of their specs and accepted the spreadsheet as the final deliverable. And although I have added some features over the years they are still using that piece of software to size the equipment and predict its performance. I realized at this point that using the worksheet as a place to specify the inputs and receive the outputs made an awful lot of sense in the engineering world. I had control over the accuracy of the calculations in the VBA code and could let the users format the worksheet as they wished. And even though VBA code is interpreted (rather thanpiled and ed) it was plenty fast enough for the frequently iterative heat transfer and fl dynamics calculations that I needed for my day job. Over the next 2 years I programmed virtually every calculation I needed to do more than once in my engineering job using this paradigm of worksheet as user interface for calculations. I have yet to find an engineering problem that can be solved quickly enough to keep the user engaged. And I have no reservations at all rmending that approach as the answer to the question about the best way of using Excel as an interface only and running calculations outside of Excel.

Excel file formats Format Extension Description Excel Workbook .xlsx The default XML-based file format for Excel 21 and Excel 27. Cannot store Microsoft Visual Basic for Applications (VBA) macro code or Microsoft Office Excel 4. macro sheets (.xlm). Excel Macro-Enabled Workbook (code) .xlsm The XML-based and macro-enabled file format for Excel 216 Excel 213 Excel 21 and Excel 27. Stores VBA macro code or Excel 4. macro sheets (.xlm). Excel Binary Workbook .xlsb The binary file format (BIFF12) for Excel 21 and Excel 27. Template .xltx The default file format for an Excel template for Excel 21 and Excel 27. Cannot store VBA macro code or Excel 4. macro sheets (.xlm). Template (code) .xltm The macro-enabled file format for an Excel template Excel 21 and Excel 27. Stores VBA macro code or Excel 4. macro sheets (.xlm). Excel 97- Excel 23 Workbook .xls The Excel 97 - Excel 23 Binary file format (BIFF8). Excel 97- Excel 23 Template .xlt The Excel 97 - Excel 23 Binary file format (BIFF8) for an Excel template. Microsoft Excel 5. Workbook .xls The Excel 5. Binary file format (BIFF5). XML Spreadsheet 23 .xml XML Spreadsheet 23 file format (XMLSS). XML Data .xml XML Data format. Excel Add-In .xlam The XML-based and macro-enabled Add-In format for Excel 21 and Excel 27. An Add-In is a supplemental program that is designed to run additional code. Supports the use of VBA projects and Excel 4. macro sheets (.xlm). Excel 97-23 Add-In .xla The Excel 97-23 Add-In a supplemental program that is designed to run additional code. Supports the use of VBA projects. Excel 4. Workbook .xlw An Excel 4. file format that saves only worksheets chart sheets and macro sheets. You can open a workbook in this file format in Excel 21 but you cannot save an Excel file to this file format. Works . spreadsheet .xlr Spreadsheet saved in Microsoft Works .. Note This format is supported in Excel Starter only. Text file formats Format Extension Description Formatted Text (Space-delimited) .prn Lotus space-delimited format. Saves only the active sheet. Text (Tab-delimited) .txt Saves a workbook as a tab-delimited file for use on another Microsoft Windows operating system and ensures that tab characters line breaks and other characters are interpreted correctly. Saves only the active sheet. Text (Macintosh) .txt Saves a workbook as a tab-delimited file for use on the Macintosh operating system and ensures that tab characters line breaks and other characters are interpreted correctly. Saves only the active sheet. Text (MS-DOS) .txt Saves a workbook as a tab-delimited file for use on the MS-DOS operating system and ensures that tab characters line breaks and other characters are interpreted correctly. Saves only the active sheet. Unicode Text .txt Saves a workbook as Unicode a character encoding standard that was developed by the Unicode Consortium. CSV ma delimited) .csv Saves a workbook as ama-delimited file for use on another Windows operating system and ensures that tab characters line breaks and other characters are interpreted correctly. Saves only the active sheet. CSV (Macintosh) .csv Saves a workbook as ama-delimited file for use on the Macintosh operating system and ensures that tab characters line breaks and other characters are interpreted correctly. Saves only the active sheet. CSV (MS-DOS) .csv Saves a workbook as ama-delimited file for use on the MS-DOS operating system and ensures that tab characters line breaks and other characters are interpreted correctly. Saves only the active sheet. DIF .dif Data Interchange Format. Saves only the active sheet. SYLK .slk Symbolic Link Format. Saves only the active sheet. Note If you save a workbook in any format all formatting is lost. Other file formats Format Extension Description DBF 3 DBF 4 .dbf dBase III and IV. You can open these files formats in Excel but you cannot save an Excel file to dBase format. OpenDocument Spreadsheet .ods OpenDocument Spreadsheet. You can save Excel 21 files so they can be opened in spreadsheet applications that use the OpenDocument Spreadsheet format such as Google Docs and Apache OpenOffice Calc. You can also open spreadsheets in the .ods format in Excel 21. Formatting might be lost when saving and opening .ods files. PDF .PDF Portable Document Format (PDF). This file format preserves document formatting and enables file sharing. When the PDF format file is viewed online or printed it retains the format that you intended. Data in the file cannot be easily changed. The PDF format is also useful for documents that will be reproduced by usingmercial printing methods. Note This format is not supported in Excel 27. XPS Document .xps XML Paper Specification (XPS). This file format preserves document formatting and enables file sharing. When the XPS file is viewed online or printed it retains exactly the format that you intended and the data in the file cannot be easily changed. Note This format is not supported in Excel 27

if I ran the Excel team here is my list in no particular order. Add a date chooser so you can select a date from a calendar. Change the undo stack so it is unique to a workbook instead of global across all workbooks. Link Min and Max of chart axis to a cell or formula. Add a NULL() function Support for RegEx Update Power Query tobine all sheets in a workbook (likebining Sheet 1 from all workbooks in a folder). Add an option to set AutoSave to off for all workbooks Add a First or Last calculation to pivot tables for fields (group by Account Number and give me the First(Customer Name) Add the ability for Power Map (3D Maps) to show a path or route. Add Center Across Selection to the Merge & Center drop down. Make the Fill Handle logic work when copying sheets if you Ctrl-Drag Feb to the right call the new sheet Mar instead of Feb (2). Add Python support Fix bugs in the new Ivy charts. Make slicers work on the iPad. Let the 3rd argument of VLOOKUP be negative to grab a value to the left of the key.