If you have a task that you perform repeatedly, then you can record all the events to achieve that task (keystrokes, mouse clicks … etc.) in a macro.
To record a macro in Excel 2007 follow these steps.
1. Click on the Developer tab. If the Developer tab is not visible, then check this post on how [...]
If you have two workbooks, or more, and you want to merge or consolidate those workbooks into one, then you may find it a tedious task to move the worksheets manually.
The following macro reads two workbooks and copy all the worksheets into a new consolidated workbook. If you have more than two workbooks as input [...]
If you want to determine whether a specific cell has a formula or a value then you need to check a range property called “HasFormula”. Here is an example where the border color of a cell is changed to red if it has a formula, otherwise it is set to black.
Dim myRange As String
myRange = [...]
If you have a workbook with a number of worksheets, some visible and other hidden, and you want to print only the hidden sheets, then you can use the macro shown below.
Because Excel will not allow the macro to be printed while it is hidden, this macro will make the sheet visible, print it and [...]
If you have a workbook with a considerable number of worksheets, some visible and other hidden, and you want to print only the visible sheets, then you can use this macro:
Sub PrintVisibleSheets()
‘***************************************************
‘Print only visible sheets in the active workbook [...]
If you know Visual Basic programming then you can create your own VBA macro and run from within Excel.
Follow the steps below:
1. On the Tools menu select Macros.
2. On the Macro submenu select Visual Basic Editor.
3. You will be switched [...]
I have added to the downloads page a yearly calendar template. This is a dynamic template that will allow you to generate a calendar for any year between 2001 and 2016. There are two combo boxes for the user to select the year and the start date of the week. Each selection will trigger a [...]
If you look at many of Excel lookup functions you will find out that they either return a value or a position of an item. There is no function that returns the address where a lookup value is found.
Below is a code for a user defined function that takes two arguments: a lookup value and [...]
I have added to the downloads page a dynamic monthly calendar template. There are three combo boxes for the user to select the month, year and the start date of the week. Each selection will trigger a macro that will generate the calendar. Here are a few notes to consider:
To Enable Macros:
Select : Tools —> [...]
When you create a macro or a user defined function in a workbook, by default that macro will only be visible for that specific workbook.
If you have a code that you use repeatedly and you want it to be available for all workbooks then you have to create a global workbook called Personal.xls and save [...]
