Create multiple Graphs on one sheet form one pivot table on another sheet
I need to create 25 different graphs (All on one sheet) from one Pivot table's data (different sheet), without the pivot table creating multiple different pivot tables. Also, the graphs need to update every month when the pivot table is updated. How is this possible? Currently all data are copied and pasted into different tables and then graphs created from each table. Thank you Kind regards Simone Fick9 quick tips to improve your VBA macro performance
As part of our work to help enterprises upgrade to Office 365 ProPlus, we have found that some users have been experiencing slow running VBA, which can be frustrating. The good news is that there are steps which can be taken to improve performance. This post aims to raise awareness of the steps that you can take to improve the performance of your macros, whether you are an end user, IT admin, or developer. We’ve collected the following steps from blog posts, Microsoft field engineers, and Microsoft MVPs like Charles Williams and Jan Karel Pieterse. 1. Turn off everything but the essentials in VBA One of the first things to do when speeding up VBA code is to turn off unnecessary features such as animations, screen updating, automatic calculations and events while your macro is running. These features can add extra overhead and slow down the macro, particularly if the macro is modifying many cells and triggering lots of screen updates and recalculations. The below code sample shows you how to enable/disable: Manual calculations Screen updates Animations Option Explicit Dim lCalcSave As Long Dim bScreenUpdate As Boolean Sub SwitchOff(bSwitchOff As Boolean) Dim ws As Worksheet With Application If bSwitchOff Then ' OFF lCalcSave = .Calculation bScreenUpdate = .ScreenUpdating .Calculation = xlCalculationManual .ScreenUpdating = False .EnableAnimations = False ' ' switch off display pagebreaks for all worksheets ' For Each ws In ActiveWorkbook.Worksheets ws.DisplayPageBreaks = False Next ws Else ' ON If .Calculation <> lCalcSave And lCalcSave <> 0 Then .Calculation = lCalcSave .ScreenUpdating = bScreenUpdate .EnableAnimations = True End If End With End Sub Sub Main() SwitchOff(True) ‘ turn off these features MyFunction() ‘ do your processing here SwitchOff(False) ‘ turn these features back on End Sub 2. Disabling Office animations through system settings Animations can be disabled across Windows by accessing the Ease of Access Center. Animations can be disabled in Excel specifically, under the Advanced or Ease of Access tab, within the File > Options menu. Please see the following link for more information: https://support.office.com/en-us/article/turn-off-office-animations-9ee5c4d2-d144-4fd2-b670-22cef9fa025a 3. Disabling Office animations through registry settings Office animations can be disabled across multiple computers by setting the appropriate registry key via a group policy setting. HIVE: HKEY_CURRENT_USER Key Path: Software\Microsoft\Office\16.0\Common\Graphics Key Name: DisableAnimations Value type: REG_DWORD Value data: 0x00000001 (1) Warning: Using Registry Editor incorrectly can cause serious, system-wide problems that may require you to re-install Windows to correct them. Microsoft cannot guarantee that any problems resulting from the use of Registry Editor can be solved. Use this tool at your own risk. 4. Removing unnecessary selects The select method is common to see in VBA code, however it is often added to the macro where it isn’t needed. Select can trigger cell events such as animations and conditional formatting which slow the macro down, so removing unnecessary selects can significantly speed up your macro. The following example shows the code before and after making the change to remove unnecessary selects. Before Sheets("Order Details").Select Columns("AC:AH").Select Selection.ClearContents After Sheets("Order Details").Columns("AC:AH").ClearContents 5. Using the With statement to read object properties When working with objects, use the With statement to reduce the number of times object properties are read. The following example shows the code before and after making the change to use the With statement. Before Range("A1").Value = “Hello” Range("A1").Font.Name = “Calibri” Range("A1").Font.Bold = True Range("A1").HorizontalAlignment = xlCenter After With Range("A1") .Value2 = “Hello” .HorizontalAlignment = xlCenter With .Font .Name = “Calibri” .Bold = True End With End With 6. Using ranges and arrays Reading and writing to cells in Excel from VBA is expensive. There is an overhead that is incurred every time data moves between VBA and Excel. This means that you should try to reduce the number of times you pass data between VBA and Excel. This is where ranges are useful. Instead of reading and writing to each cell individually in a loop, read the entire range into an array at the start, loop through the array, and then write the entire array back at the end. The following example code shows how a range can be used to read and write the values once, instead of reading each cell individually. Dim vArray As Variant Dim iRow As Integer Dim iCol As Integer Dim dValue As Double vArray = Range("A1:C10000").Value2 ‘ read all the values at once from the Excel cells, put into an array For iRow = LBound(vArray, 1) To UBound(vArray, 1) For iCol = LBound(vArray, 2) To UBound(vArray, 2) dValue = vArray (iRow, iCol) If dValue > 0 Then dValue=dValue*dValue ‘ Change the values in the array, not the cells vArray(iRow, iCol) = dValue End If Next iCol Next iRow Range("A1:C10000").Value2 = vArray ‘ writes all the results back to the range at once 7. Use .Value2 instead of .Text or .Value There are different ways that you can retrieve values from a cell, and which property you use can make a different in the performance of your code. .Text is commonly used to retrieve the value of a cell – it returns the formatted value of a cell. Getting the formatting of a cell is more complex than just retrieving a value, and makes .Text quite slow. .Value is an improvement over .Text, as this mostly gets the value from the cell, without formatting. However for cells formatted as a date or currency, .Value will return a VBA date or VBA currency (which may truncate decimal places). .Value2 gives the underlying value of the cell. As it involves no formatting, .Value2 is faster than .Value. .Value2 is faster than .Value when processing numbers (there is no significant difference with text), and is much faster using a variant array. For a more detailed explanation, please see Charles William’s blog post, “TEXT vs VALUE vs VALUE2”: https://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/ 8. Bypass the clipboard (copy and paste) When you use the Macro Recorder to record operations that use copy and paste, the code will use the copy and paste methods by default. However, within VBA code, it is much faster to bypass the clipboard and use internal operations instead. By default, copying will copy everything, including formulas, values and formatting. You can make copying faster by only copying values or formulas, without the formatting. The following example shows the code before and after making the change to bypass the clipboard. Before Range("A1").Select Selection.Copy Range("A2").Select ActiveSheet.Paste After ‘ Approach 1: copy everything (formulas, values and formatting Range("A1").Copy Destination:=Range("A2") ‘ Approach 2: copy values only Range("A2").Value2 = Range("A1").Value2 ‘ Approach 3: copy formulas only Range("A2").Formula = Range("A1").Formula If you still find that a macro takes longer than expected to execute many individual copy and paste operations, you may want to apply the following hot fix: https://support.microsoft.com/en-in/help/2817672/macro-takes-longer-than-expected-to-execute-many-individual-copy-and-p 9. Use Option Explicit to catch undeclared variables Option Explicit is one of the available Module directives in VBA that instructs VBA how to treat the code within the code module. Setting Option Explicit requires all variables to be declared and will give compile errors if an undeclared variable is used. This helps catch incorrectly typed variable names and improves performance with all variable types being defined at compile time, instead of being inferred at runtime. This can be set by typing: Option Explicit at the top of each module in your project or by checking the "Require Variable Declaration" option under Tools -> Options in the VBA editor. Additional Details on Module directives can be found here: https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/option-explicit-statement Conclusion We hope that this has helped highlight some of the ways that you can make your macros run faster. We’re sure that we haven’t covered everything, so please comment below with any other tips or tricks to improve the performance of your macros in Excel. Further reading https://blogs.office.com/en-us/2009/03/12/excel-vba-performance-coding-best-practices/ http://datapigtechnologies.com/blog/index.php/ten-things-you-can-do-to-speed-up-your-excel-vba-code/ https://www.ozgrid.com/VBA/SpeedingUpVBACode.htm FAQs: After using the new macro animations stopped working/it’s stuck on manual calculation. It is possible the code disables the various settings, but the macro crashes before re-enabling these settings. To fix this, you will need to run the code to enable these settings again. Other resources https://www.thespreadsheetguru.com/blog/2015/2/25/best-way-to-improve-vba-macro-performance-and-prevent-slow-code-execution - The Best Way To Improve VBA Macro Performance And Prevent Slow Code Execution https://www.ozgrid.com/VBA/SpeedingUpVBACode.htm - Optimize Slow VBA Code. Speeding Up Slow Excel VBA Code About the co-authors Charles Williams founded Decision Models in 1996 to provide advanced consultancy, decision support solutions, and tools based on Microsoft Excel and relational databases. Charles is the author of FastExcel, the widely used Excel performance profiler and performance toolset, and co-author of Name Manager, the popular utility for managing defined names. For more information about Excel calculation performance and methods, memory usage, and VBA user-defined functions, visit the Decision Models Web site. Jan Karel Pieterse is a long time Excel MVP who develops custom solutions focused on Microsoft Office, with deep expertise in Excel and VBA. He runs the website JKP Application Development Services site, where you can find an interesting collection of articles, training events, and utilities. For a good overview of topics, see this list of in-depth articles. Jan develops some cool and useful utilities for Excel, including NameManager, RefTreeAnalyser, and Flexfind. You can find a full list on the downloads page.
Autuomatically adding Rows with a formula
Is there a way to use formulas to add rows based off a number that i input? I am using excel and a printing software that automatically prints each cell and a label and assigns a QR code. The products we create a panel Hurricane Shutter system. I have an excel sheet that automatically combines the opening(window,door,etc) name with the part number, so i have to manually input the name and number. since the amount of part numbers is always consistent, i feel i should be able to speed things up a little bit. Example. If i want to use a Shutter that has one panel, i know that it will always have 5 parts. So i have to manually input into 5 different rows, 10 different cells. Window1, 1A, Window1, 2A, Window1, 3A, Window1 4A, Window 1 5A. If i want to use a shutter that has two panels, i will have to create 7 parts, (Window1 6A, Window1 7A) etc. I would like to go into one row, input the name of the my window and number of parts and have it automatically generate the requested amount of rows with the 7unique inputs. If youre not understanding what i saying, or just curious, this may help. The link to our sales page. basically everything part on this blueprint would have its own QR code that would lead it to this website. https://frontrowhurricaneshutters.com/sales-ph32 (Dont let this distract from the main question, but one way i speed up the process now is that i have a table with the generic part numbers that i use to quickly input. I have to manually input the name(window1), but then i just copy the table so speed things up a little bit. But im looking to speed things up still)
Excel 2016 Workbook Printing Double Sided problem
I got a new Windows 10 computer. Previously, I have used Excel 2016 with no issues. What I need to do is print a workbook in Excel (multiple worksheets) double sided. For example, sheets 1-3 are on one tab, while sheet 4 is on another. When I print, I would generally click, "print entire workbook" which then takes my document from 3 pages to 4. Pages one and two print double sided no problem, but no matter what I do, pages 3 & 4 will not print duplex. Other than printing to pdf (which saves it, then I have to open that document and print from there which is a PAIN), can I print those 4 pages onto 2, or am I stuck? I have searched the internet to no avail. Thanks!
Excel: Auto-populate outputs based on input change in a table
I have a very complex financial model made up whose output changes based on the 2 inputs. I want to make a table where the column has all possible values of input 1 and row has all possible value of input 2. Each cell in the table should have the output corresponding to the two inputs. How do I go about this? Can you provide a link or a function that I can use to do this?Create one list of names combining three lists
Hi I am sure I have done this before but I can't find it now. I have a spreadsheet with three lists. I want to combine the lists into one. On the sheet attached I have three columns of names A, B and C. In D, I would like to list all the same names in one long list. Thank youMap Chart - Multiple Series
For filled map we naturally can't show multiple series at once, here is one of the ways how we may shift from one to another. I took first figures I found, internet usage in EU countries, and limit them by three countries: Next we 'unpivot' these data copying each series one under another, add one more column to the right (Category) with the name of the series, convert all together into the table (Ctrl+T) and insert Category slicer for it: Select first two columns of the table as above and insert the chart Since the chart will be placed against filtered rows not to forget to fix its position Desirably to have the name of filtered category as separate value. Usual approaches are to have filter value from PivotTable (but we don't need it) or use SUBTOTAL / AGGREGATE which can work with hided cells (or VBA of course). Details are out of this topic, let add array formula (that's not the only variant of it) =INDEX(tMaps[Category],MIN(IF(SUBTOTAL(3,OFFSET(C2,ROW(tMaps[Category])-ROW(C2),0)),ROW(tMaps[Category])-ROW(C2)+1))) where tMaps is the name of our table, into the cell F2 (will be under our chart). It returns first unfiltered value for Category column. Next hide Category column and design the Slicer a bit: To show selected series on the chart do Select Data for it and assign series name on cell F2 was defined above Practically that's all, result looks likeexport a hyperlink in a csv file
I am trying to create a csv file to import into a website I am building, I need the csv to include a cell which includes a hyperlinked word in html format. I have a column which contains urls and I need to create another column which includes the html. So cell A2 contains https://example.com. I want cell A3 to contain <a href="https://example.com">Preview</a> I am a bit lost, any help is appreciated
