It might not seem like a big deal at first, but macros can save you a huge amount of time if you do the same series of steps on a regular basis.
It might be formatting raw data, filtering and sorting information, or applying the same series of functions and operations to your sheets. You can just write a macro, send the spreadsheet over, and let them work with it. Recording a macro is how you tell Excel which steps to take when you run the macro. In our spreadsheet, we have a list of names and a corresponding list of their sales for the month:.
Then, in Main Tabs, make sure that Developer is checked:. Click OK , and open up that tab in the main window. Keep in mind that there are already a lot of Ctrl-based shortcuts, so try not to overwrite any of those that you use regularly. You can also choose to save the macro in one of three places: the current workbook, a new workbook, or your personal macro workbooks. Your personal macro workbook personal. But if you think you may want to reuse that macro on another workbook, keep it in your personal macro workbook for easier access.
All we need to do is click Record Macro , take those actions, and then click Stop Recording. The Stop Recording button replaces the Record Macro button when you start recording. Just hit record, take some actions, and then stop recording.
First, you can run it from the Ribbon. You will need this password to unprotect the worksheet. If you have some or all of the worksheets protected, you can just use a slight modification of the code used to protect sheets to unprotect it. Note that the password needs to the same that has been used to lock the worksheets. While it does the work, when cells are merged you will not be able to sort the data.
In case you are working with a worksheet with merged cells, use the code below to unmerge all the merged cells at one go. A lot of time, you may need to create versions of your work.
These are quite helpful in long projects where you work with a file over time. Using timestamps will allow you to go back to a certain file to see what changes were made or what data was used.
You need to specify the folder location where you want to save the file. You can specify something related to your project or company.
If you work with data for different years or divisions or products, you may have the need to save different worksheets as PDF files. In the above code, I have specified the address of the folder location in which I want to save the PDFs. Also, each PDF will get the same name as that of the worksheet. Use this code when you have a worksheet that contains a lot of formulas and you want to convert these formulas to values. Here is the code that will lock all the cells that have formulas, while all the other cells are not locked.
In case you want to unprotect all the worksheets, use ws. Unprotect instead of ws. Protect in the code. Similarly, you can modify this code to insert a blank column after every column in the selected range.
For example, you may want to track activities such as when was a particular expense incurred, what time did the sale invoice was created, when was the data entry done in a cell, when was the report last updated, etc. Use this code to insert a date and time stamp in the adjacent cell when an entry is made or the existing contents are edited.
Note that you need to insert this code in the worksheet code window and not the in module code window as we have done in other Excel macro examples so far. To do this, in the VB Editor, double click on the sheet name on which you want this functionality.
Also, this code is made to work when the data entry is done in Column A note that the code has the line Target. You can change this accordingly. Highlighting alternate rows can increase the readability of your data tremendously. This can be useful when you need to take a print out and go through the data. Note that I have specified the color as vbCyan in the code. You can specify other colors as well such as vbRed, vbGreen, vbBlue.
While you can run the spell check by hitting the F7 key, there is no visual cue when there is a spelling mistake. Note that the cells that are highlighted are those that have text that Excel considers as a spelling error.
If you have more than one Pivot Table in the workbook, you can use this code to refresh all these Pivot tables at once. You can read more about refreshing Pivot Tables here. While Excel has the formulas to change the letter case of the text, it makes you do that in another set of cells. Note that in this case, I have used UCase to make the text case Upper. You can use LCase for lower case. In this case, I have used vbBlue to give a blue color to the cells.
You can change this to other colors if you want. Once created, you can have this macro in the Quick Access Toolbar or save it in your personal macro workbook. In this code, I have specified the blank cells to be highlighted in the red color. You can choose other colors such as blue, yellow, cyan, etc. Read more on how to sort data in Excel using VBA. Assign a macro to a button. Create and save all your macros in a single workbook. You want to automate a repetitive task in Excel, so that you can do the task again with a single click.
I'll record an auto-fill operation where I build a series of days based on typing Sunday in a cell. After I type Sunday, I right-click the cell and drag it to the right. As I move over here, the Tool Tips show what will go in each cell, all the way through Saturday. When I get over here, I release the right mouse button and click Fill Series. I'll repeat these steps, but this time, I'll turn on the macro recorder.
I click the Record Macro button. Let's name this macro, "FillDays". Macro names can't contain spaces. I click OK to start the recorder. See how the Record Macro button turned to Stop Recording. When I'm done, I'll click that. We are done with our auto-fill, and since that's all we wanted to record, I'll click Stop Recording. Now I'll clear these filled cells to clean up the worksheet, and select A1 again so we can test our macro.
I'll click the Macros button to bring up the Macro dialog box, and click Run. I'll clear these cells again, and this time I'll select a cell in a different column and row C3, just to test the macro a little more. By using relative references, the macro will build a series by selecting the active cell and 6 cells to its right, instead of building a series using A1 through G1.
In the next video, we'll rewrite the "FillDaysRelative" macro manually in an Excel tool called the Visual Basic Editor , so that it'll do something a little bit different.
Table of contents. Work with macros. Table of contents Work with macros.
0コメント