Today I would like to share with you the best tip I learned last year. Why has this had never occurred to me previously? I have no idea. But I can honestly say that this has already saved hours of my life. Given the long hours we are expected to work these days, I know … Read more
VLOOKUP is one of the most powerful functions in Excel. It allows you to look up a value from a table based on a search term. A simple example would be a where you had a long list of employees and their telephone numbers. You could use VLOOKUP to find the telephone number of any … Read more
A tolerance chart shows how a result compares to a maximum and minimum permitted range. These charts are often used in industry to check whether a process is working correctly and within permitted limits. The image below shows the type of chart we will be creating. The data This chart requires various values: Minimum value … Read more
A couple of weeks ago we were preparing for a presentation. Much of the presentation is constructed from linked Excel worksheets into PowerPoint. This technique is great because the presentation automatically updates each time the Excel worksheets change. In the presentation, there are also some PowerPoint tables in the default blue striped layout. Then came … Read more
Manipulating ranges and cells is one of the most common actions in VBA. You can use the following to either learn from or just to copy and paste into your own code. Referencing ranges & cells from the worksheet ‘Reference range by address Workbooks(“WorkbookName.xlsx”).Worksheets(“SheetName”).Range(“A1:D4”).[Other properties and actions] ‘Reference cells by row and column – Cells(Row,Column) … Read more
Have you ever wanted to display a character in Excel which isn’t on the keyboard? Maybe you’ve wanted to use a ÷ or maybe an ü, but you just weren’t able to find it. A standard keyboard can produce 96 characters, but lots fonts include up to 255 characters. Most countries have different keyboard layouts to … Read more
Working with workbooks is one of the most common actions when writing VBA code. The following is a reference guide to for finding the right syntax. Contents: Referencing workbooks by name Referencing workbooks by their status Referencing workbooks by when they were opened Assign a workbook to a variable Create new workbooks Opening workbooks Counting … Read more
Working with worksheets is one of the most common actions when writing VBA code. The following is a reference guide to for finding the right syntax. Referencing worksheets from the workbook ‘Reference a worksheet in another workbook Workbooks(“WorkbookName.xlsx”).Worksheets(“SheetName”).[other properties/actions] ‘Reference a worksheet in the same workbook as the VBA code ThisWorkbook.Worksheets(“SheetName”).[other properties/actions] ‘Reference a worksheet … Read more
PDF files are pretty much the norm for distributing reports these days. They provide a nice easy way to collate documents from different sources for distribution. However, once a document is in a PDF format getting that information back into a usable form is a nightmare. If we try to copy and paste a data table … Read more
Looping through every worksheet or every workbook is a very common action. One which you may be doing on a regular basis. The code below should set you on the right path. Loop through every worksheet Sub LoopThroughWorksheets() ‘Create a variable to hold the worksheet and also the output message Dim Ws As Worksheet Dim … Read more