add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
The best way to write a program is simple: write, make mistakes, fix bugs, but most important – do not be afraid.
Writing a Program to Consolidate Data Tables
Sheets 1, 2 and 3 (January, February and March) have tables containing balance data for three months.
The required program is the consolidation of the three tables into a single data table in Sheet 4, one table below the other. In order to identify which data belongs in which table, you have to insert the name of the table in an additional column. With a consolidated table, you can sort, filter, insert subtotals, and/or create PivotTables.
Sound complicated? Follow the workflow and the lines of code one step at a time.
January Sheet Consolidate Tables Sheet
Stage 1: Enter text into a cell
In Cell C1 (header), in all three sheets, enter the text Month.
You do not have to select the sheet. In the lines of code, refer to the address of the sheet and the cell.
Stage 2: Enter the name of the table
In every sheet, in Column C, in every cell that corresponds to the items (Column A), enter the name of the table. In the example, table name = sheet name.
Follow the steps:
1. Select the sheet January.
2. Jump from Cell A1 to the last cell in the range (Ctrl+down arrow).
After End, you can choose to move in four directions – xlDown, xlToRight, xlToLeft.
3. Select a corresponding cell in Column C; that is, offset the selection two cells from the active cell.
Use Offset to move between cells. The first number in parentheses represents the number of rows, and the second number represents the number of columns. In the example, you offset the selection two columns to the right.
4. Enter the name of the sheet in the cell range, from the active cell, cell C14 (refer to the sheet January in the figure), until cell C2.
In the selection of the cell range, the comma symbol means until. That is, from the active cell (ActiveCell) until a cell with the specific address C2.
Alternatively, you can enter text in the cells. For a range, write the text between quotation marks.
5. To summarize, here are the lines of code for Stage 2.
6. Copy the lines of code and paste them twice. In the first row, change the name of the sheet from January to February, and in the next, change it to March.
Another technique is to select a range that corresponds to the selection of the cell range in the column. Offset the selection two columns to the right and select.
Stage 3: Copy and consolidate the tables
From the sheet January, copy the table, and paste it into the sheet Consolidate Tables.
1. Select the sheet January.
2. Select the data table. Use the Excel shortcut Ctrl+* for selecting the current region.
3. Copy the current region that you selected.
4. You can combine steps 2 and 3 into a single line.
5. Select the Consolidate Tables sheet, select a cell, and paste.
6. After copying, clear the clipboard. Look at the last line; it is the same as pressing the Esc key.
7. Copy the tables from the sheets February and March. From these sheets, copy the data range without the header row.
8. Select the sheet February.
9. Select the data without the header row.
To copy the data range without the header row, you have to select the data range from Cell A2 until the last cell in Column C.
10. Paste the data in the Consolidate Tables sheet. In order to paste, you have to select the first empty cell in Column A, under the data tables you pasted earlier.
11. Repeat this technique and paste the data for the table for the month of March.
Another technique is you can copy data from the sheets February and March by copying the rows from Cell A2 until the end of the data range.
To select the rows instead of copying them, use Select instead of Copy.
To select entire columns, use
.
Your final code should look like this:
Sheets("March").Select
Range("A1").End(xlDown).Select
ActiveCell.Offset(0, 2).Select
Range(ActiveCell, "A2").Copy
Sheets("Consolidate Tables").Select
Range("A1").End(xlDown).Select
ActiveCell.Offset(1, 0).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Stage 4: Finish and run
You have finished. Now run the macro one step at a time by using F8. You have fixed all the bugs. Now run the macro by inserting a button into the Consolidate Data sheet.
Reducing the lines of code
Exchanging repeated actions with loops
In the program that you ran, you typed extraneous lines of code. You pasted the name of the sheet in Column C in all three sheets, and you copied the data and pasted it three times.
Loop no. 1
In Column C, enter the name of the sheet.
Define a variable of the integer type. Call it i.
In the line of code Sheets(i), i is the number of the sheet. It is assumed that the sheet January is the first sheet in the workbook, the sheet February is the second, and so forth.
Loop no. 2
Insert an additional loop to copy the data into the Consolidate Data sheet.
Before you run the loop, copy the header row to the Consolidate Data sheet. Then, the loop performs a circular action of copying the data to the Consolidate Data sheet.
Making code more efficient
Do not select objects
Recorded macros are notorious for performing tasks inefficiently. The recorded macro will almost always select an object and then perform an action on the selected object, in two lines, as follows:
This is rarely necessary. Changing the selection takes precious time in the macro. You can usually specify the action directly to the range without selecting it. The following line is equivalent to the above lines:
Do not select sheets
It is not necessary to select worksheets. If you want to perform actions on a sheet other than the ActiveSheet, you simply qualify each Range command with the name of the worksheet:
Before:
After:
Combine copy and paste into a single statement
Immediately after you copy a range, you can add a parameter that tells Excel where to paste the results. The following five lines can be reduced to a single line:
Before:
After:
Eliminate redundant loops
Our program loops through the three sheets twice. With a little advanced planning, we should be able to fill in the month names and copy in the same loop. This will eliminate the need for the second loop. Also, there is no need to change the CutCopyMode to False until the end of the program.
Find the last used row using End ()
Excel worksheets offer 65,536 rows of data. The best way to find the last used row in column A is to start at cell A65536 and hit END up arrow. The code for this is as follows:
Range("A65536").End(xlUp)
The resulting macro contains just eight actual lines of code.
Sub ReallyShort()
Dim i As Integer
'Copy the headings
Sheets("January").Range("1:1").Copy Destination:= _
Sheets("Consolidate Tables").Range("1:1")
Sheets("Consolidate Tables").Range("C1").Value = "Month"
'Loop through the three worksheets
For i = 1 To 3
Sheets(i).Range("C1").Value = "Month"
' Add Month Name to Column C
Sheets(i).Range(Sheets(i).Range("C2"), Sheets(i).Range("A65536"). _
End(xlUp).Offset(0, 2)).Value = Sheets(i).Name
'Copy A2:C? to the next row on Consolidate Table sheet
Sheets(i).Range(Sheets(i).Range("A2"), Sheets(i) _
.Range("A2").End(xlDown)).EntireRow.Copy _
Destination:=Sheets("Consolidate Tables") _
.Range("A65536").End(xlUp).Offset(1, 0(
Next i
Application.CutCopyMode = False
End Sub
Copying data from another workbook (opened or closed)
So far, the program you ran copied data from the active workbook. When you copy from another workbook, there are special considerations such as determining if the source workbook is open.
Switching between open workbooks
To move between workbooks, use either of these examples:
or
Note
Look at the lines of code. After the filename, there is no xls extension. Check the settings for displaying files in the window. If the files in the Window menu are displayed with the xls extension, add the extension to the file name.
Switching to a closed workbook
To open a workbook:
How can I tell if the workbook is open or closed?
If a macro is run and it contains lines of code that are supposed to copy data from an open or closed workbook, it is essential to first run a check to see whether the workbook containing the data is open. This check helps to avoid errors (it is possible that you forgot to open the workbook before you ran the macro). This check is performed through a function.
Add the check to the macro by using IF. The IF command sends a query to the function to calculate whether the workbook is open. If it is, the function returns TRUE.
Note
Notice the line of code for opening a workbook. At the end of the line, there is an addition:
.
This addition prevents the Update Links message from being displayed.
The function:
I copied the data and I want to close the workbook I opened
To save a workbook after updating it:
To close a workbook:
To close the workbook without saving it, while suppressing the warning that the workbook needs to be saved:
Option 1
Option 2
We can fool Excel into thinking the workbook has already been saved and can therefore be closed.
I want to save the data in a new sheet in the workbook
To insert a new sheet:
To insert and change the sheet’s name:
I want to save the data in a new workbook, change its name and save it in a folder
To add a new workbook:
To save the workbook in the My Documents folder:
The data in the Consolidate Tables sheet is not formatted
The data you copied was pasted into the Consolidate Data sheet.
In the Consolidate Data sheet, the width of the columns is not uniform or does not automatically fit, according to the text/number in the cells. The numbers in the data columns are not formatted.
Automatically fitting the column width:
Setting the column width:
Formatting numbers in Column B:
Use Paste Special to copy formatting. Copy the formatting of Column A to Column C:
I would like to define a name for a table to consolidate data
Select the data table and define a name for it.
Add a name to the data range
Use this line of code to define a name for a cell, a row, a column, a sheet, or another cell range.
Now that you have finished, you will certainly want to print
Define the print area. For example, the current region will be printed.
For printing, change the number of copies: