Jul
9
Creating Effective Forms with Excel – The Worksheet Form (Part 2 of 3)
- July 9th, 2012
Note: this article was written specifically for Excel 2010 and its Ribbon interface, however the concepts reviewed will apply for the most part to earlier versions.
In our last article we discussed how Excel is a fantastic tool for creating forms, and reviewed the native Data Form, which is built into Excel (but you need to work to find it in Excel 2007+). It’s a simple tool for entering transactional data, like check register or vehicle service information, but isn’t a great tool for one-off purpose-built forms that generally only need to capture your information one time, like an application form, employee expense report or time-off request. The next form type we’ll discuss is the worksheet form, in which you model a worksheet to mimic what you would see with a printed form. In fact, many companies make forms like this in Excel so they can replace pre-printed forms, and it saves quite a bit of expense, both from not having to send something to a commercial printer, as well as being able to capture data electronically (although tragically, most companies that employ digital forms – in Excel or specific form building software applications – never do anything more with that digital information). Another key point about this article is that we’ll be talking about how to design an intelligent user-entry form for one-time use, not being able to actually capture the data in a transactional sense, like keeping a record of invoices you generate. That is easy enough to do, but it requires VBA (Visual Basic for Applications) code, which is outside of the scope of this article. However, there will be a link to a Company Invoice template form that can capture transactional data that you’re free to modify for your own use.
As mentioned in the last article, the key to intelligent form design is making it as easy as possible for your users to give you the information that you want. If you make it hard for them to give it to you, they will make it hard for you to get it out. It’s really as simple as that. This isn’t necessarily by any conscious decision on their part, it’s just a fact that if you make it difficult for someone to give you information, they generally won’t be inclined to spend a whole lot of time error checking their entries. We’ve all had negative experiences filling out one form or another, so that leads to two primary principles in design: first, make it easy and intuitive for people to enter information (data), and second, structure your form in such a way that it flows from point to point in a natural progression. Here’s a simple example for entering phone numbers: most people will enter “(212) 555-1212”, and waste time with the parentheses and hyphen, when Excel has a Telephone number format that will accept “2125551212” and automatically format it as “(212) 555-1212”. Do you have to educate your users to a certain extent, sure, but they’ll thank you once you do, and that can cascade down to how they interact with other intelligently designed forms & applications in the future. In fact, intelligent design extends far beyond just filling out forms, but how people interact with applications in the first place.
With that in mind we’ll be covering various elements native in Excel to be able to facilitate efficient design and user input:
- Worksheet Protection – Using some of Excel’s internal functionality to direct data entry, and prevent them from overwriting important formulas or other information.
- Data Validation (to limit or direct user entry) – For instance in a Time-Off request you wouldn’t want to let a user enter a date before today. Or using lists to limit what a user can select, like all of the US states, instead of giving them the opportunity to misspell Mississippi (or in one case I saw not too long ago: “Missasagua”, and that’s probably misspelled).
- Conditional Formatting – You can use this to indicate to a user that a cell needs to have an entry.
- Cell Formatting – Just like the telephone number formatting example.
- Worksheet Formulas (to prefill entries based on a Data Validation selection) – Let’s say you have a customer list in a worksheet, you can use VLOOKUP to pre-fill the customer address information as soon as the user selects Customer Name from a Data Validation list.
- Worksheet Controls – Check Boxes, Option Buttons, and other web-type features that make it easier for your users to fill out a form faster. An example would be a “Tax Required” button on an Invoice form that could then be used to calculate the appropriate tax.
None of the methods we’ll be discussing are infallible, the data/input that you receive is only as good as the users giving it, and there will always be mistakes. But if you can limit that on the front end, then you greatly simplify both your life and theirs, and you can all subsequently spend more time on more important things.
Worksheet Protection – What is that anyway? Well, by default all cells in an Excel worksheet (over 1 billion now) are protected. Which means that if you protect a worksheet, then user entry into any cell is restricted. But you can use that to your advantage, so let’s say that you want to allow a user to enter information into certain cells, but not alter others, then you simply unlock those cells and protect the worksheet. Once you do that users can only enter information into those unprotected cells.
NOTE: you can select multiple, non-contiguous cells with CTRL+Left-Click, then CTRL+1 launch the Format Cells dialog. From there goto the Protection tab and uncheck the “Locked” check box. When you protect the worksheet afterwards, then just those cells will be open for entry. The Hidden option is for formulas; when you select that then users will only see the results of formulas, as opposed to the actual formulas themselves. This can be very handy if you have novice users who might get confused when they see formulas (or if you have a very complex formula that you don’t want to share).
Unlocking Cells
An added bonus to Worksheet Protection is that it introduces a natural Tab order (meaning that your users can use the Tab key to move from each unprotected cell to the next). A key note here with regards to design is that the natural Tab order goes from Left-to-Right, then back down to the next row and does not deviate, so you need to keep that in mind with your “intelligent” design. By way of example, think of any Internet form you’ve filled out where you tabbed to different fields in the wrong order, requiring you to redirect to go back to the right place. What’s that do? Wastes time right? While it might not seem consequential, think of a company like Verizon with 200,000 employees. If they have a poorly designed form that even only 20% of the staff use, and they spend an extra x minutes per week/month working with an inefficiently designed process then you can only imagine the cascading effect that can have on their internal efficiency, and subsequently why our rates go up each year. Another thing that taking advantage of the natural Tab order does is keep your user’s fingers on the keyboard instead of transitioning to the mouse to move from cell to cell. Anytime you can eliminate wasted motion like that is a good thing and again, it might seem inconsequential, but it adds up over time. And time goes two ways, you waste it or save it, but you can’t get it back.
Once you’ve selected the cells you want users to be able to enter data into you can goto the Review Tab and select Protect Sheet. Or in the vein of efficiency, use the keyboard shortcut ALT+T+P+P, both of which will display the Protection dialog:
Worksheet Protection Options
Beginning with Excel 2003 Microsoft added significantly more protection options, some of which you might find helpful beyond just locking/unlocking cells. Deciding which options to allow is entirely up to you, and it may well very change from form to form, so it’s well worth experimenting with the options to see how they all work.
Standard worksheet protection with unlocked cells should suffice for most forms, but it does take some thought when you plan the form to ensure that you can follow the Tab Order directionality. There are other ways to allow tabbed behavior using Defined Name Ranges and/or VBA (Visual Basic for Applications) code, but that’s another discussion. You’ll find more details on worksheet protection in this article: Automating Data Entry and Protecting your Data.
Data Validation – This is one of the most powerful tools you have when it comes to building powerful, user-friendly and intelligently designed forms. Data Validation allows you to direct a user to only input specific information, like select from a list of States, input a certain range of numbers, or dates. For example, if you have an Employee Time-Off request, you wouldn’t want someone to be able to enter a day before today. Or you might have an Annual Employee Performance Appraisal and want to limit a recommended annual merit increase to a certain percentage. For more information on how to set up Data Validation, see our previous article here: Automating Data Entry with Data Validation Lists.
The example workbook for this article is a very simple customer information form. Here is an example of using Data Validation to allow a user to select from a list of Titles:
Data Validation List
Notice how the cells requiring are shaded orange, but the Data Validation cell where “Mr.” has been selected is white? That brings us to our next topic, which is Conditional Formatting.
Note: Excel internally recognizes Data Validation input on a worksheet, and it’s captured with the VBA Worksheet_Change event, which can be a very powerful tool for directing workflow based on a user’s selection.
Conditional Formatting - Once you’ve set up your form, and for this article we’re using a very simple example, you can use Conditional Formatting to focus the user’s attention on where they need to enter data. This won’t stop someone from submitting your form with incomplete information (you can do that with VBA), but it serves as a visual clue that they need to fill in certain fields. To employ Conditional Formatting, simply select the cells that you want shaded (HINT: this can be combined with the steps to unlock cells, as you have already selected the cells, so why repeat it?), goto the Home tab and select Conditional Formatting, or use the keyboard shortcut ALT+O+D. In this case we applied a very simple condition that simply checks to see if each cell has a value of nothing (“”) or not. If the condition is true, then Excel applies the format, otherwise it won’t. There are a lot of features available in Conditional Formatting, far too many to review here, so please spend some time getting used to it. Beginning in Excel 2007 Microsoft greatly enhanced your capabilities with this tool, so it’s well worth getting to know. For instance, you were previously limited to 3 conditions, now you have 64. Although please think conservatively, as actually having that many conditions could cause just as much confusion as not.
Conditional Formatting
Note on Quotes: when you initially enter =”” in the Conditional Formatting Rule criteria and confirm it with OK/Apply you probably won’t see anything happen, which might lead you to believe that you did something wrong. You didn’t, but Excel will initially change the formula to =””””””, so first hit Apply, then go in and Edit the rule and remove the additional quotes that Excel added. Unfortunately, this is by design as Excel has a hard time with quotes (and apostrophes), as they have internal functions as well as what you’re trying to do with them.
Cell Formatting – The next element of form design is using custom cell formats to your advantage. It’s all too common to see users fill out forms using literal formats, like entering “(212) 555-1212”, which is unnecessary, as you can tell Excel to do it for you. For forms especially, there’s even a series of “Special” options that have been pre-built for you. In this case we’ll select Phone Number. Now all your users need to do is enter “2125551212” and Excel will automatically format it for you. Cell formatting also preserves the integrity of a value, whereas user entered punctuation can render it nearly useless as Excel will then read it as text. It is possible to get to the value with the use of some formulas, but why go to that extent if you can avoid it up front? The key is to use Excel’s tools so that it works for you, as opposed to you working for Excel (which happens a lot). Fortunately, Excel’s pretty smart, so it knows a lot of things, like “$123.45” is a number even though the $ has been added by hand, but you should never count on it, just like you shouldn’t anticipate that users won’t make mistakes (especially since we all do).
Format Cells - Phone Number
Worksheet Formulas – You can use formulas to your advantage in Excel forms, and it is one of its most powerful features. While we’re not going to get into a full-blown formula discussion here, as there have been entire books devoted to the subject, we’ll cover a few concepts to give you an idea of what can be done, which is primarily having Excel perform calculations so your users don’t have to do it themselves (can you say error management?). Let’s go back to the Employee Performance Appraisal example. Imagine that you have a list of all employees on another worksheet (Employee ID, Name, Hire Date, Hourly Salary, etc.), you can use formulas to populate all of that information simply by selecting the Employee ID from a Data Validation list. Here’s an article on IF statements & LOOKUPS, which allow you to do that. You can also use a method called Concatenation to combine information from certain cells and create unique text strings. For instance, let’s say that your employee e-mail naming convention is “LastName.FirstName@Company.com”, and you have First Name in cell A1 and Last Name in cell B1. You can use:
Which would create “Jones.Bob@XYZco.com”. The key here is the Ampersand (&) operator, which lets you link both cell references and values. Since that formula creates a text string you can coerce it to be a hyperlink by using the following:
=HYPERLINK(B1&”.”&”A1”&”@CompanyName.com”)
This brings up an interesting point that if you try to do this with numeric references you’ll lose any number formatting you might have applied as Excel now sees that data as text, but you can use the TEXT function to coerce it back within your new text string. The following example lets you concatenate text and today’s date all within one cell:
=”Report Printed on: ”&TEXT(TODAY(),”mm/dd/yyyy”)
Which would read: “Report Printed on: 07/09/2012”. Otherwise you’d see: “Report Printed on: 41099” (41099 is Excel’s way of storing the date value). Pretty slick, huh? I liken this to Excel knows that I’m on Windows 7, it knows my system’s regional preferences, but it doesn’t know my intention with the value I’m referencing, even if I’ve already formatted the a referenced cell properly, so it runs home and converts the value into its basest element, in this case the system date value.
Worksheet Controls - The final element to worksheet forms is using worksheet controls, of which there are two distinct categories: Form Controls & ActiveX Controls. These are items like Check Boxes, Option Buttons, List & ComboBox controls, etc., all of which can be used to simplify form use. For example, you could add a Check Box to a form for “Tax Required?”. Once checked it would trigger a formula that will calculate the Tax due on a purchase order. For the purpose of this article, we’re only going to discuss Form Controls, as they can be formatted and controlled within Excel itself. ActiveX controls on the other hand are VBA components and you generally need to write code against them in order to expose their full capabilities. While they are more flexible and robust than their Form control counterparts, they are more difficult to master.
The first thing you need to do is expose the Controls themselves, which are located on the Developer tab in the Ribbon. As this also exposes VBA elements, Microsoft chose to hide this Ribbon element by default, since unfortunately most Excel users will never use it. In Excel 2010 goto FileàOptionsàCustomize Ribbon and check the “Developer” check box in the right-hand pane. In Excel 2007 this is a check box under the General options.
Show the Developer Tab on the Ribbon
Once you confirm this you’ll now see the Developer tab appear on the right side of the Ribbon. From there you’ll see a Ribbon group called Controls, and clicking on the Insert button will display the controls that you have available:
Like Conditional Formatting and Formulas, there are so many options here, that we’re just going to briefly touch on the topic with a general example. But you should experiment with as many of the controls as you can. Once you do you’ll probably find yourself using them all the time, even for your own work. One of the most common controls to use is the Button. Buttons are great because you can assign macros to them to perform certain tasks, like print a form once it’s been filled out. What’s really cool is that you can assign a macro to almost any object. Form Controls are objects, but so are any of the shapes you’ll find from the InsertàShapes Ribbon group. I prefer to use shapes to assign macros to since they’re much more visually appealing, and while it may seem inconsequential, people will actually fill out a well-designed and appealing form much faster and with less errors than one that‘s poorly designed.. Which would you rather see:
Form Button vs. a nice Shape Button
Building a good form not only takes functionality into consideration, but needs to appeal to the end user as well. Just think about how much easier your tax forms would be to fill out if they were thoughtfully designed.
Note: in the example workbook both buttons have a macro assigned to them to display the worksheet form in Print Preview mode, so you’ll need to enable Macros in order for them to work. You’ll find an Introduction to Macros article here: Automating Tasks with the Macro Recorder – First Steps.
For our working example we’ll add a Check Box to our form to indicate whether our contact wants to receive opt-in e-mails. From the Form Control dialog simply select the Check Box control (the Form control, not ActiveX), then draw it on your worksheet with the mouse.
Check Box Form Control
From here you can right-click the Check Box to gain access to its internal controls and formatting. Obviously you want to edit the text to suit your needs, but you can also do things like linking the control to a particular cell. When checked the control has a value of “TRUE”, and when unchecked its value is “FALSE”, so you can perform calculations against that. Most of the time you’ll put the linked cell in either a location away from the form itself, or format its text color to match the form background so it doesn’t display. If a user doesn’t need to see the TRUE/FALSE value, then don’t show it to them. There’s no need to confuse people by displaying something that they don’t need to see.
Format your Form Control
In this case the Check Box is linked to cell B11, which will toggle/display TRUE/FALSE depending on whether it’s checked or not. As you see in the Value options, you can decide whether the Check Box’s initial state should be Checked or Unchecked. A quick note on formatting is that you can’t change the font size with Form controls. That’s controlled strictly by the active worksheet’s Zoom setting. On the other hand, you can manipulate font attributes with ActiveX controls.
Using a Formula based on a Check Box's value
In this example the formula in cell A15 simply reads:
=”This customer “&IF(B11=TRUE,”does”,”does not”)&” want to receive e-mail communication.”
This is another example of using concatenation to create custom messages for your users, or perform calculations that they might otherwise need to do manually. Granted, these are simple examples, but they should give you an idea of what you can do. If anything it will help you realize how you can set things up in a time-saving and well thought out fashion to make it easier to collect information from people. The other thing it does is greatly reduce errors, which directly equates to bottom line savings.
Review – in this article we discussed the various elements that you can employ to streamline data collection processes without having to purchase an expensive purpose-built form creation software application simply by using tools readily available to you in Excel. You’ll find loads of great example forms on the internet that are free for you to use, modify and distribute as you see fit. Most notably is the Microsoft Template Gallery, which you’ll find here: http://office.microsoft.com/en-us/templates/. The key points to remember about building effective forms in Excel are relatively simple:
- If you make it hard for people to give you data, then they’ll make it hard for you to get it, so be creative, and design forms with the end user in mind. If you focus on HOW the user interacts with your form, then the data flow will be easy. If you focus on just getting the information with little or no regard to their interaction/process then you’re all in for a miserable experience.
- A well-built and intelligently designed form can greatly reduce errors, and allow your users to give you information much faster than if they had to write it out by hand, or perform manual calculations on their own.
- Use the tools that you have available to you, Worksheet Protection, Data Validation, Conditional and Custom Cell Formatting, and Worksheet Formulas and Controls.
Chris “Smitty” Smith is Director of Training and a Developer for the boutique consulting firm www.excelandaccess.com
Jul
8
Creating Effective Forms with Excel – The Simple Data Form (Part 1 of 3)
- July 8th, 2012
Have you ever gotten an online form from a company or government organization and received a PDF that you had to print out and fill out by hand (and you’ve probably even seen one that looks like a scan of a scan of a poorly built form on an old IBM typewriter)? What’s the point? If the point is to be able to use an electronic medium, then what purpose is served by making you print something and fill it out by hand? Not only does that process waste your time, think about what happens on the other end: you print out the form, fill it out, scan/fax/mail it, then when it gets back to the other side what do they do? Put all of that information back into a computer. What a monumental, no colossal, waste of time (not to mention the time that needs to be spent trying to error check your/my lousy handwriting)! So why do people make it so hard to get information from each other? The answer isn’t simple, but the solution is:
Build a fillable form!
There are many software applications that are purpose built for form building, but unless you build forms for a living you need not go to the expense. If you have Microsoft Office, which most of us do, you can easily build a form in Excel, which is ubiquitously seen in the business world as a form tool (oh, and some of us use it for financial analysis). But it’s an amazingly effective form building tool, and allows us to keep information in the electronic/digital environment, after all, once information is put into a computer, there’s no need for that work to be redone by hand!
So what is a form? Simply put, a form is a medium into which you enter information. We all have to fill them out, DMV registration, passport applications, opening bank accounts, online flight bookings, etc. But what’s the biggest problem with doing we generally encounter with any of these processes? The lack of intelligent design and structure is the problem. Printed/non-digital forms notwithstanding, have you ever gone to an online form to buy something, or book a flight and the tab order is out of sequence, or not logical at all? (For those of you who navigate forms with the mouse, here’s a hint: use the TAB key) We all have, and it’s frustrating, but it can all be avoided if you simply understand that the easiest way to get concise information from people is to make it easy for them to give it to you. If you make it hard for them to do that, then they will make it hard for you. So which would you rather do? Save time and make it easy, or make the experience painful for everyone? Unfortunately, too many forms are seemingly built with the latter in mind.
Like it or not, data collection and management is central to almost everything we do, be it keeping track of personal finances, managing aspects of a business, or even booking a vacation. Unfortunately, most organizations make it difficult for you to give and get information (big corporations and governments most notably), so we’re going to discuss how to keep it simple with one tool that most of us have at our fingertips. Excel.
There are two primary types of forms and the way in which you use them. The first is a form that allows you to capture continuous information, like keeping track of a check register or vehicle service history. This type of information is stored with columns holding relevant transactional details, like Date, Check Number, Payee, Amount, etc., while rows store individual records, or transactions. The second is a single use form, like an employment application, invoice or little league sign-up. This type of form usually requires input in various places on a worksheet. Note that a single-use form can be created in such a way as to capture transactional details, but doing so requires VBA (Visual Basic for Applications) programming, which we’re not going to discuss as part of this series.
In this article we’re going to discuss the simplest way to create a continuous form in Excel, which is to use the DataàForm method (a built-in functionality in Excel). However, it’s not very robust, and only supports a limited number of data fields, but it can be a great tool for allowing people to quickly enter data. Unfortunately, it’s one of those features that Microsoft chose to hide in Excel 2010, so you have to find it. We’ll show you that in a minute, first here’s an example of the Data Form:
That’s about as easy as it gets, but it’s not robust in that there’s no way to prevent users from entering incorrect information, so this should only be used for very simple data collection needs. Otherwise you can spend as much time validating what your users entered as you would entering it by hand, knowing what needs to go where, and how. Note in the example how the data is clearly structured with each column storing a different type of information, while each row represents a unique record. One nice thing about this structure is that it readily lends itself to being represented as a Pivot Table or Chart, which are great ways to analyze your data, and display it graphically.
Note: good data at the end is only as good as the data going in!
To expose the Data Form tool in Excel 2010 goto the Quick Action Toolbar above the Ribbon, select the drop-down for more options, then the More Commands option.
QAT
Once you do that the Customize Ribbon dialog will appear, in the left-hand pane select All Commands, then find “Form” and drag it to the right hand pane like you see here:
Customize the QAT - More Commands
Add the Data Form to the QAT
From here on out, anytime you’re in a data set that Excel can recognize when you click on the Form button the Data Form will appear. But there are some simple rules for using this tool: first, your data needs to be structured in such a way that Excel can read and understand it (in much the same way that your users need to understand what’s expected of them). So your worksheet needs to be set up in a contiguous manner, meaning that if you have empty columns or rows in your worksheet, then don’t expect Excel to pick it up (then again, don’t expect your users to either). Second, you need to have column headers for the form to be able to identify, and as you saw in the Data Form example, Excel uses the first row in your worksheet to define the entry fields. Third, if you have more data fields then the form can accept, this won’t be the appropriate tool for you (but we’ll discuss that in the next article). Fourth, and key to intelligent form design is to layout your worksheet in such a way that makes sense for data collection and use once you get it, which means that individual records should ideally be stored in a series of rows going down the worksheet, not columns going across, as columns should be used for individual components of each overall record (row). Also think about Excel’s capabilities: while you now have over 1 million rows of data, you “only” have 16,000 columns. If you were to collect data in columns as opposed to rows, you could run out of room. Unfortunately, we see this type of structure all too often, so you can avoid the pain of having to rebuild a workbook just by building it right in the first place. Here’s an example of the simple Data Form in use:
Data Form Example
Notice that your column headers do have Accelerators attached to them. Those are the underlines beneath the S in Store, a in Date, etc. To quickly acces any of those fields directly from the keyboard, all you need to do it press ALT+ the corresponding letter for that field. I’ve never tried to push one of these forms to its field limit, but I’d imagine that you’re going to run out of Accelerators when you get beyond 26 fields (corresponding with 26 letters in the alphabet).
If you do like the Data Form, but need a more robust version, longtime Excel MVP John Walkenbach has a free add-in he created called the Enhanced Data Form. You can find it here: http://spreadsheetpage.com/index.php/dataform/home.
John Walkenbach's Enhanced Data Form
You can find a copy of the Simple Data Form workbook here: http://sdrv.ms/OLHFSh. Note that it contains a simple macro attached to a button to display the form in case you haven’t exposed the Data Form button on the QAT, which will require you to enable macros. In versions of Excel prior to Excel 2007, the Data Form is available from the Data menu.
In the next article we’ll discuss building single-use forms, like an invoice, or employee expense report.
Jun
5
Excel: Automatically consolidate data from multiple worksheets?
- June 5th, 2012
We had a question at #ExcelHelp this week from somone who wanted to consolidate data from multiple worksheets to a master sheet. My first thougt was use Excel’s native PivotTable functionality for multiple consolidations ranges (easier than it sounds, just follow the Wizard), but this case just needed to move an inconsistent range of worksheets just a few times, so VBA code seemed more appropriate for a one-off situation like this where you copy data from each worksheet to the master. The problem (especially if you’re just starting with VBA) is that one worksheet might have 52 rows of data and the next 73, or 1,008. So how do you deal with dynamic ranges like that? Unfortunately, this isn’t one that the Macro Recorder can take care of for you, so you need to go beyond that and actually start writing some code of your own, but this is where VBA’s power shines; you just need to know how to tap into it…
What might seem very complicated, really just needs a bit of thought and some dynamic ranging, so we can identify the range of data in each worksheet to be copied (A2:A1274 let’s say). But that just defines the copy from range, then we need to identify the first empty row in the worksheet where the data is going to be copied, and each time new rows of data are added, we need to find the new last row. Within all of that we’ll go through each worksheet and then redifine those ranges.
Here’s the resulting code, with comments so you can see what it’s doing:
Sub CopyfromSheets()
' Declare Variables for Worksheets & lr to get the last used row in a range
Dim ws As Worksheet
Dim lr As Long' Loop through each worksheet in the workbook
For Each ws In ActiveWorkbook.Worksheets
' Tell the code to ignore the "Master" worksheet
If ws.Name <> "Master" Then
' Find the last used row in column A in each sheet
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
' Rows.Count counts the number of rows in the worksheet and doesn't care if it's 65,536 or 1MM+.
' End(xlUp) goes from the last row at the bottom up to the last row at the top
' You can also use End(xlDown), but it will fall apart if you have blank rows in your data
' .Row gets the Row #, so "lr" can then be 52, 74, 1,008, etc., as it resets for each worksheet
For Each ws In ActiveWorkbook.Worksheets
' Tell the code to ignore the "Master" worksheet
If ws.Name <> "Master" Then
' Find the last used row in column A in each sheet
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
' Rows.Count counts the number of rows in the worksheet and doesn't care if it's 65,536 or 1MM+.
' End(xlUp) goes from the last row at the bottom up to the last row at the top
' You can also use End(xlDown), but it will fall apart if you have blank rows in your data
' .Row gets the Row #, so "lr" can then be 52, 74, 1,008, etc., as it resets for each worksheet
' Copy/Paste - As long as you're not trying to Paste Special, like Values, then you can have Range.Copy -->Destination all on one line
' In the Paste part we use Offset(1) to move down to the next Empty row so you don't paste over existing data
ws.Range("A2:A" & lr).Copy Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1)
' Exit the IF statement evaluating the sheet name
End If
' Move on to the next worksheet. This doesn't care how many sheets you have, as it will go through them all
' If you only wanted to do this on 20 of 100 sheets you can define the For Each Next construct like this:
' For i = 10 to 30
' Sheets(i)...
' Next i
' Which goes from sheet #10 to sheet #30 and ignores any others
Next ws
End Sub
' In the Paste part we use Offset(1) to move down to the next Empty row so you don't paste over existing data
ws.Range("A2:A" & lr).Copy Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1)
' Exit the IF statement evaluating the sheet name
End If
' Move on to the next worksheet. This doesn't care how many sheets you have, as it will go through them all
' If you only wanted to do this on 20 of 100 sheets you can define the For Each Next construct like this:
' For i = 10 to 30
' Sheets(i)...
' Next i
' Which goes from sheet #10 to sheet #30 and ignores any others
Next ws
End Sub
So now you have a way to dynamically copy data from multiple worksheets to a master sheet. And while this is a relatively simple example, it wouldn’t be hard to set it up to copy multiple columns. You could even set it up for a variable number of columns by adding the same methodology used to identify the last row:
lc = Cells(1,Columns.Count).End(xlToLeft).Column
May
7
Excel VBA Training & Certification
- May 7th, 2012
If you’ve followed any of our posts, then you’ll see that we’re huge proponents of using VBA (Visual Basic for Applications) as a means of automating everyday tasks in the Office environment.
One of the most frequent forum comments/requests that we see is for how to learn VBA. Most of the advice that you’ll get starts with your most valuable free tool, which is the macro recorder. Then there are lots of online tutorials you’ll find via Bing/Google, and finally you can buy some books (John Walkenbach’s Power Programming with Excel VBA is the best). But what if you want to step up your game and actually get certified in VBA?
Well, look no further than http://www.vbaexpress.com/training/. The folks over there have done an amazing job of not only creating fantastic VBA courseware, but they offer the ability to be certified in VBA as well.
If you want to step up to your “A” game then you can use the following coupon codes to get a discount:
SP10OFFTRAINING and SP5OFFCERTIFICATION
A word of caution here though: I’ve had the pleasure of being a tech editor for some of the content and it’s not for the faint-of-heart. This is serious stuff. But you’ll without a doubt walk away being the best VBA user around. And while we’re not tax professionals, we’ll bet good odds that it’s tax deductible.
