Export Multiple Sheets to a Single PDF File – Excel VBA

This tutorial will demonstrate how to export multiple sheets of a workbook into a single PDF file using Excel VBA. The code can be seen below and adapted for your own use.

I have previously written a tutorial on exporting all the sheets of a workbook to PDF using VBA. This macro will allow you to be more specific as to what sheets are exported. It will also export to a single PDF file, rather than a separate file for each sheet. Continue reading

Loop Through Subfolders using FileSystemObject

A common Excel VBA requirement is to work with different files and folders of a directory. It may be that you need to search and open a file, list the names of files from a folder, print all the files from a folder, or even delete files.

The different tasks you may need to perform are endless, but fortunately Excel VBA provides an easy way to access and perform operations on the files and folders of a directory. This easy way is known as FileSystemObject.

To use FileSystemObject, otherwise known as FSO, you first need to create an instance of FileSystemObject. You can then access some of the objects from its model such as File and Folder to perform different tasks. Continue reading

Highlight Max and Min Values on a Column Chart

When using column charts to compare values, you may want to highlight the maximum and minimum values on the chart. By highlighting these columns it removes any confusion when trying to view the top and bottom values.

Highlighted max and min values

Finding the Max and Min Values

To show the max and min values on a column chart, we will first need to identify the max and min values of our range. These values will then be used as a second data series when we create the column chart. Continue reading

Email Workbook as an Attachment – Excel VBA Macro

The functionality in Excel to send a workbook via email as an attachment is fantastic. However if you find yourself using this feature often, you may have a desire to automate or speed up the process.

I have been asked this many times in my Excel VBA classes so thought it would be beneficial to share some code to accomplish this.

This Excel VBA Macro will attach the current workbook as an attachment to an email and send it. It will add some text to the subject line and body of the email also.

This code can be adapted to your own needs. I will explain how to do this and the lines you will need to change. There are also a few things to look out for that may cause you problems.

This macro should work for any email service. I have used it with my GMail and my Yahoo accounts successfully. The code may just need some tweaking to work for your own situation. Continue reading

Use VLOOKUP to Find the Last Match in a List

VLOOKUP is an awesome Excel function. We all know this. But it certainly has its limitations. One of these is that it can only return the first match from a list.

This is great when looking for a unique value. But what about when the value you are looking for appears multiple times in the list, and you want to return the last match.

Sure we could sort the list so that the last match would become the first, but this is not always an option.

This blog post looks at using the VLOOKUP function to return the last match in a list. The technique and formulas used can be adapted to find the 2nd or 3rd match if required.

VLOOKUP to find last match

Continue reading

Pick Names at Random from a List – Excel VBA

In this blog post we will explore how to pick names at random from a list using Excel VBA.

Suppose we were picking the names for a draw or competition and needed to generate a list of maybe 3, 5 or any number of names from a list.

These names must be selected at random and be unique. You cannot select the same name more than once.

The macro code shown below can be copied and pasted into a VBA module and adapted for your use. Comments have been used to identify the key and more complex parts of the macro.

This macro uses an array variable to store the names being randomly selected from the list. Every time a name is selected, it is checked against this array to ensure that it has not already been selected. If it has, then another name is randomly selected. Continue reading

Extract the Domain from a List of URL’s

If you are responsible for analysing a list of URL’s in Excel, then you may need to extract the domain from each URL. After extracting the domain you can perform useful reporting tasks such as grouping the domains, or counting the number of unique domains in the list.

This can be quite a difficult task especially if the URL’s are not consistent. In this article we explore a few different approaches to extract the domain from a URL. The method you choose will depend on the appearance of the URL’s in the list and the format you would like to extract the domain in.

This article covers;

  1. Extract the domain including the URL protocol.
  2. Extract the domain ignoring URL protocol.
  3. Extract the domain without the www.
  4. Extract root URL’s that don’t end with a slash (/).
  5. Using helper columns to break up complex formulae.

Continue reading

Pick a Name at Random from a List

In this blog post we look at how to pick a name at random from a list. There are many reasons you may need this kind of random selection from a list.

It may be a neat way of deciding who is next to do the washing up, or to make the tea :). Or it is useful for randomly drawing teams for a knockout cup competition.

To perform this random selection, we are going to use two different functions together. They are INDEX and RANDBETWEEN.

Continue reading

How to Use the TEXT Function in Excel

TEXT Function Examples and Description

The TEXT function is used to convert a numeric value into text in a specified number format. This function is really useful when concatenating strings that will contain a numeric value.

Take the example below, where the following formula has been entered into cell C2. The date formula loses its format when concatenated. This is now useless.

=A2&" - "&B2

Date loses formatting when concatenating Continue reading

Excel Timesheet with Different Rates for Shift Work

When creating a timesheet in Excel you may need to calculate different rates of pay. This could be because of overtime, or the type of work being done.

In this tutorial we create a timesheet that uses different rates of pay for working weekends, and also night shifts.

Prefer a video tutorial? Skip to the video.

Calculating Hours Worked

In this timesheet, the day and night shifts are entered on separate rows. This will make it easier for our calculations. A column (column E) is also used to record whether it is a day or night shift.

Excel timesheet with different rates for shift work

Our first task is to calculate the number of hours worked on the shift. To do this the following formula can be used. This formula finds the difference between the two times as a decimal, and then multiplies by 24 to convert it to hours.

(D5-B5)*24

If the shift is worked overnight, so you started work on one day but finished the next, then the formula below is used.

(1+D5-B5)*24 Continue reading