Excel Print Macros

Have you ever recorded the print settings for a macro to print a set of reports? You can see an example recording below. At first glance the process of making the print settings and sending the report to the printer look unbelievably complicated. Do not despair, read on…

Sub Macro1()

Range("C3:E8").Select

ActiveSheet.PageSetup.PrintArea = "$C$3:$E$8"

With ActiveSheet.PageSetup

.PrintHeadings = False

.PrintGridlines = False

.PrintComments = xlPrintNoComments

.PrintQuality = 600

.CenterHorizontally = False

.CenterVertically = False

.Orientation = xlLandscape

.Draft = False

.PaperSize = xlPaperLetter

.FirstPageNumber = xlAutomatic

.Order = xlDownThenOver

.BlackAndWhite = False

.Zoom = 100

End With

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub

The Macro Recorder is not selective; it has recorded the state of every control in the Page Setup dialog box. You just need to delete the unnecessary statements and you will see that the code the code for printing macros can be quite dramatically simplified and reduced.

The PrintOut method is all you need for the actual printing part of your macro. As usual the command is “Object.Method”. The object is what you want to print, for example to print a range of cells you give the cell references. There is no need to select the range or set it as the print range first.

This is all you need for printing:

Sub ConcisePrintMacro()

'Print a range of cells.

Range("A1:G250").PrintOut

'Print the used range of the active worksheet.

ActiveSheet.PrintOut

End Sub

The page setup settings refer to the worksheet where the range of cells is located. Here, the object is the worksheet’s page setup settings. For example to set the report to landscape orientation, the instruction is:

ActiveSheet.PageSetup.Orientation=xlLandscape

You have to repeat the object reference for each print setting that you need to make; orientation, headers, footers, print titles etc. Or you can use the “With” keyword to make the reference once and then start each instruction with a dot, as in the example below:

Sub PageSetupSettings()

With ActiveSheet.PageSetup

.CenterFooter = "My Report"

.RightFooter = "by Anon E. Mouse"

.Orientation = xlLandscape

.FitToPagesWide = 1

.FitToPagesTall = 1

End With

Range("A1:G250").PrintOut

End Sub

Good luck with your printing.

How Easy is Copying?

Copying data from one location to another is one of the most common activities in Excel macros but the Copy method has a secret that is not always appreciated.

This is an example of a recorded macro. Select a range of cells, copy it to the clipboard, select a destination range and paste from the clipboard. Finally, press the ESC key to clear the clipboard.

Sub RecordedCopyAndPaste()

Range("C4:E11").Select

Selection.Copy

Sheets("Sheet2").Select

Range("D7").Select

ActiveSheet.Paste

Application.CutCopyMode = False

End Sub

As usual with recordings, every single action is recorded step-by-step. You can simplify your code quite easily and remove the unnecessary instructions. For example, it is not necessary to make a selection first before the cell data is copied or cut, just refer directly to the cell range:

Range("C4:E11").Copy

Range("C4:E11").Cut

Watch out when pasting, the cell does not have a Paste method, the following instruction will fail:

Range("C4:E11").Paste

Use Paste Special instead, like this:

Range("C4:E11").PasteSpecial.

But the reason why there is no Paste method for a cell is very simple, you do not need one. The Copy method has an optional argument, “Destination” and you can copy and paste all in one instruction. In fact, you copy directly from one cell to another and avoid using the clipboard entirely. Make sure that you get the instruction entered correctly, there is a space after the “y” of “Copy”:

Range("C4:E11").Copy Destination:= Range("G10")

You enter the line without the argument descriptor, like this:

Range("C4:E11").Copy Range("G10")

Use can use square brackets to make the range references if you prefer:

[C4:E11].Copy [G10]

Copy from one worksheet to another, either use full object references or square brackets:

Sheets("Sheets1").Range("C4:E11").Copy Sheets("Sheet2").Range("G10")

or

[Sheet1].[C4:E11].Copy [Sheet2].[G10]

As an alternative to the copy method, you can always use an equals sign to copy information from one cell to another but there is a subtle difference. For example the following statement copies the value that is displayed in cell A1 to cell B1:

[B1] = [A1]

Whereas this statement copies the formula:

[A1].Copy [B1]

Measuring areas and lists in Excel macros

In many Excel macros we need to select or measure an area of cells whose dimensions can not be predicted at design time, when you write the code. You have to allow the macro to detect the extent of the data. Here’s a few examples:

Measure the dimensions of the current block of consecutive data:

x = ActiveCell.CurrentRegion.Rows.Count

x = ActiveCell.CurrentRegion.Columns.Count

Identify the cell coordinates of this range:

x = ActiveCell.CurrentRegion.Address

Measure the dimensions of the area containing data on a worksheet:

x = ActiveSheet.UsedRange.Rows.Count

x = ActiveSheet.UsedRange.Columns.Count

Identify the first used row of the worksheet:

x = ActiveSheet.UsedRange.Row

Identify the last used row of the worksheet:

x = Cells.SpecialCells(xlCellTypeLastCell).Row

Identify the next free row starting from A1:

x = Range("A1").End(xlDown).Row + 1

Identify the first row and column in the block containing the active cell:

x = ActiveCell.CurrentRegion.Row

x = ActiveCell.CurrentRegion.Column

To select the block of cells containing the active cell:

ActiveCell.CurrentRegion.Select

Select from cell C3 to the top of the current region:

Range("C3").End(xlUp).Select

Select from cell C3 to the last cell on the right in the current region:

Range("C3").End(xlToRight).Select

Starting a New Line in Excel Macros

You either want to start a new line in your code or you want force a new line in the prompt text in an input box or a message box.

Line Continuation in Macros

Some statements are rather lengthy and difficult to read on one line. Do not press enter to wrap the text; this just produces a syntax error. To continue the same logical line onto the next physical line, introduce a line continuation character into your code. Use the following sequence of keystrokes for a line continuation character; Spacebar, Underscore, Enter. It is a sequence, not a key combination. You can have as many line continuations as you require. Second and subsequent lines can be tabbed.

Statements like this can be rather difficult to read:

ActiveWorksheet.Cells.SpecialCells(xlCellTypeVisible).Select

Statements are much easier to read with line continuation characters:

ActiveWorksheet.Cells. _

SpecialCells(xlCellTypeVisible). _

Select

Starting a New Line in a Message Box or an Input Box

The prompt text in the message does not wrap onto a new line in the box until the character count reaches 160; meanwhile the box just gets wider with the text on one line. Use any one of the following constant values to force a new line:

Chr(10),Chr(13), vbCrLf, vbCr, vbLf

Forcing new lines in the prompt:

MsgBox "Hello Charlie," & vbCrLf & "have a nice day."

The VBA Format function

If you have not yet discovered the VBA Format function then it is time that you did and then you can ruefully shake your head and mutter, "If only I had known about that six months ago…"

The Format Function will transform any numeric value. Although they differ in detail the fundamental number format codes for Excel and VBA are identical. To find the relevant code values look up Custom Number Formats in Excel Help or the Format function in VBA Help.

Format(Expression, "Format Code")

Here’s a few examples:

To format -5000 to (5,000.00):

Format(-5000, "#,##0.00_);(#,##0.00)")

To format 5,000,000 to 5.0 million:

Format(5000000,"0,,.0 million")

To return the current month number as two digits, i.e. June = 06:

Format(Month(Date), "00")

To return the name of the current month from the current system date:

Format(Month(Date), "MMMM")

To return the day of the week from the current system date:

Format(Date, "DDDD")

To express one number as a percentage of another:

Format(34 / 5000,"0%")

Using Excel Worksheet Functions in Macros

You can use VBA functions in your Excel macros and you can use Excel worksheet functions but you must call them from Excel if the function is not recognised in the VBA language. It is very easy to determine where you need to call the function from Excel.

For example, we wish to use the LEN function to measure the character length of an expression and assign the value to the x variable. LEN is both an Excel worksheet function and a VBA function. As you type the statement into the module you will see that if the function is recognised then it’s syntax diagram will appear, like this:

x=Len(Expression

However, if no prompt appears then you know that the call will fail:

X=Sum(

If the function is exclusively an Excel function then you need to include the Application object reference.

The shortcut is to just access the Application object:

x = Application.Sum( 50, 650, 100)

Excel functions are members of the WorksheetFunction Collection. The full reference will display a list of all the Excel worksheet functions after you type-in the dot operator:

x = Application.WorksheetFunction.Average( y, z)

Use Excel’s Input Box!

You can use either the generic VBA Input Box function or the Excel Application object's Input Box Method in your Excel macros. The InputBox Method allows for some entry validation using its optional Type argument and is the only one where you can point out of the box to select a range of cells on a worksheet. Invalid data entry into Excel’s Input Box is handled by the Excel application and as most of the work involved in coding Input Boxes is in the validation of the received input it is usually the best choice.

VBA Input Box Function

The generic VBA function does not have any facility for validating the user's input, this has to be done in your code. The result of the function can be directly assigned to a cell but it is usually better assigned to a variable so that it can be effectively evaluated.

Range("A1") = InputBox("Please enter the date.", _

Title:="Current Period", _

Default:=Date)

Excel Input Box Method

You will notice the difference between the two when you enter an invalid input. So long as you have completed the Type argument, Excel will handle any invalid input and you only have to test for the Cancel button in your code. The Cancel button for the Input Box function returns a zero length string whereas the Cancel button for the Input Box method returns FALSE.

Range("A1") = Application.InputBox("Please enter the date.", _

Title:="Current Period", _

Default:=Date, _

Type:=1)

The Type argument specifies the return data type. It can be one or a sum of the values shown in the list below:

0 - Formula

1 - Number

2 - Text

4 - True or False

8 - Cell reference

16 - Error value

64 - An array of values

Only the Excel InputBox Method allows you to point out of the box to return a range reference. In the example, return data type 8 is specified and the input box will accept a range reference either by typing or dragging through the cells.

Sub ExcelInputBoxMethod()

Set MyRange = Application.InputBox( _

Prompt:="Please select a range.", _

Title:="Colour me Red", _

Type:=8)

MyRange.Interior.ColorIndex = 3

End Sub

When you assign a variable value using an Input Box never set the data type before the input has been received and validated. To avoid Type Mismatch errors, declare the variable as Type Variant and then use Type conversion functions after the input has been captured and validated.

In the following example the USD variable has to be of Type Currency. Had the initial declaration been As Currency then the code would produce a Type Mismatch error when the Input Box received invalid data and before the input could be evaluated in the loop:

Sub MisMatchErrors()

Dim USD As Variant

Do

USD = InputBox("Enter the USD rate:")

Loop Until IsNumeric(USD) = True

USD = CCur(USD)

End Sub

Dynamic Arrays in VBA Macros

A dimensioned array has to be declared using a constant value; however this constant value maybe unknown at the point of declaration. Use the ReDim statement instead of Dim to create a dynamic array; one that can be re-sized at run time.

The following example creates an array of worksheet names:

Sub ArrayofSheetNames()

Dim iNumShts As Integer

Dim i As Integer

'Calculate the number of sheets.

iNumShts = Sheets.Count

'Size the array.

ReDim sSheetNames(1 To iNumShts) As String

'Populate the array.

For i = LBound(sSheetNames) To UBound(sSheetNames)

sSheetNames(i) = Sheets(i).Name

Next

'Add another sheet.

Sheets.Add

'Resize the array.

ReDim sSheetNames(1 To Sheets.Count) As String

'Repopulate the array.

For i = LBound(sSheetNames) To UBound(sSheetNames)

sSheetNames(i) = Sheets(i).Name

Next

End Sub

In the previous example you will have noticed that we had to repopulate the array after having resized it. ReDim resizes the array but clears the data already stored. Use ReDim Preserve when you want to resize an array but retain the data previously stored. ReDim Preserve is particularly useful when you want to gather some information and store it in an array but do not know the extent of the data. In the following example a range of cells is being searched and we want to store the cell references of the cells containing a certain value.

As the data is found, it is stored in the array and then an extra element is added to the array ready for the next item of data. When the search is completed the array has one element too many; this is then removed.

Note the use of ReDim at the start of the procedure to initialise the array variable; this has to be done so that the UBound function can calculate the size of the array when the first element of data is stored.

Sub DynamicArrayOnTheFly()

Dim vList As Variant

Dim oCell As Range

'Initialise the variable so that we can

'use UBound later on.

ReDim vList(0)

'Loop through the cells.

For Each oCell In Range("A1:D50")

'Test for a value of 5.

If oCell.Value = 5 Then

'Store cell reference in array.

vList(UBound(vList)) = oCell.Address

'Add element to array ready for next item.

ReDim Preserve vList(UBound(vList) + 1)

End If

Next

'Remove empty element from array.

ReDim Preserve vList(UBound(vList) - 1)

End Sub

How to make Pivot Tables Refresh Automatically

Excel Pivot Tables do not automatically update when the source data is changed, they have to be refreshed. To make your Pivot Tables refresh automatically you need a macro to do the refreshing and an Event to trigger the macro. This seems quite an involved process the first time that you do it but it gets easier, it is mainly just copying from one workspace to another.

Recording the Macro

You have to record a macro and then copy the recorded code from the macro into one of the worksheet’s events. Firstly, the recording: Select a cell in your Pivot Table and then choose Tools, Macro, Record New Macro from the menu. Just make sure that the Store macro in: setting is This Workbook and click the OK button.

Now you have to record your refreshing the Pivot Table, so choose the Data menu and select Refresh Data. The recording is completed and you can turn off the Macro Recorder by choosing Tools, Macro, Stop Recording. The next job is to find the recorded code and copy the instruction that refreshes the Pivot Table. Choose Tools, Macro, Macros and select the recorded macro from the list and then click the Edit button on the right hand side. You will now see the Visual Basic Editor displayed and you can view the recorded procedure, which should look something like this:

Sub Macro1()

' Macro1 Macro

' Macro recorded by Anon E. Mus

ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

End Sub

Copy the line of code that refreshes the table, it should be obvious which one you need. Do not copy anything else. Now, return to the Excel worksheet workspace by clicking an Excel icon or by pressing ALT-F11.

Using an Event to trigger the Macro

Next, you need to find the Event code shells for the worksheet where your Pivot table is located. Right-click the worksheet tab for the relevant worksheet and choose View Code from the shortcut menu. You are returned to the Visual Basic Editor once again. Looking at the active window you will see two drop-down lists at the top of the window.

The drop-down list on the left hand side is the Object list, the one on the right hand side is the Procedure list. Drop the Object list and select the Worksheet object, then choose a suitable event from the Procedure list. The Activate event is probably the most suitable for our purposes. Paste your recording into the procedure so that it looks like this:

Private Sub Worksheet_Activate()

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

End Sub

The Activate event is triggered whenever you make the worksheet active, so in order to test that the procedure is working correctly you need to perform the event. Return to the Excel worksheet workspace by clicking an Excel icon or by pressing ALT-F11, select another worksheet in the workbook and then select the original worksheet where the Pivot Tables are stored. This triggers the event and you will see that the tables are automatically refreshed. Of course, you do not have to make the initial macro recording every time that you need to do this, all you need is the code for the Refresh method so you can type it in or paste it from a previous recording. But, be careful, each Pivot Table needs to be correctly described using its Name Property; this is the text value inside the brackets. In the example below the name of the Pivot Table is "PivotTable1"

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

To find out the name of a specific Pivot Table, right-click one of its cells and choose Table Options from the short cut menu. The name is shown in the top left corner of the dialog. It is quite in order to list the Pivot Tables if you have more than one which needs refreshing in which case the procedure would be like this:

Private Sub Worksheet_Activate()

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh

End Sub

Although it would be easier to have a looping procedure so that you can avoid having to look up the name of each individual Pivot Table:

Private Sub Worksheet_Activate()

Dim pt As PivotTable

For Each pt In ActiveSheet.PivotTables

pt.PivotCache.Refresh

Next

End Sub

Pivot Tables are contained by each individual worksheet so if you want to have a procedure which will refresh every Pivot Table on every worksheet in the entire workbook then you will need an outer loop for the worksheets and an inner loop to refresh the pivot tables. The code would be as follows:

Private Sub Worksheet_Activate()

Dim ws As Worksheet

Dim pt As PivotTable

For Each ws In Worksheets

For Each pt In ws.PivotTables

pt.PivotCache.Refresh

Next pt

Next ws

End Sub

It is so irritating when simple formulas return division by zero errors (#DIV/0!) and you have to spend ages changing simple formulas to complex conditional formulas to force a zero value into the cell instead of an error.

Try this macro; you select the cells containing the formulas and the macro wraps a conditional structure around the original formula. For example, if the original formula in the cell was =A1/B1, after the macro has been run the formula will be as follows, =IF(ISERROR(A1/B1),0,A1/B1)

Macros are small programs that automate repetitive processes in Excel worksheets. You do not need to know how the macro works, just copy it to the Excel file where you want to use it and then run it. See below for detailed instructions.

Sub ForceToZero()
Dim rngSelected As Range
Dim rngFormulas As Range
Dim Cell   As Range
Dim strPrompt  As String
Dim strFormula  As String

On Error Resume Next

'Select the cells to process.
strPrompt = "Select the cells to convert."
Set rngSelected = _
Application.InputBox(strPrompt, "Force Zeros", , , , , , 8)
'Just the formula cells.
Set rngFormulas = rngSelected. _
SpecialCells(xlCellTypeFormulas, 23)

'Loop.
For Each Cell In rngFormulas
'Remove the equals sign.
strFormula = Mid(Cell.Formula, 2)
'Write the new formula.
Cell.Formula = "=IF(ISERROR(" & strFormula & _
"),0," & strFormula & ")"
strFormula = ""
Next
End Sub

<strong>How to Copy and Use a Macro</strong>

Firstly, select all the text of the macro (including the line at the beginning starting with the word Sub and the line at the end, End Sub) and press CTRL+C to copy.

Then Switch over to Excel and go to the Visual Basic Editor, ALT+F11. Press CTRL+R to open the Project Explorer, select your workbook file in the listing, right-click and choose Insert, Module.

Paste the macro into the module by pressing CTRL+V.

Finally, return to Excel (press ALT+F11 again) and run the macro by choosing Tools, Macro, Macros. Select the macro from the list and click the Run button over to the right hand side.

If you like the code download the http://www.mousetraining.co.uk/ms-office-training-manuals.html manual
 

Some workbooks seem to have hundreds of Names, where did they all come from? Delete them all in a few seconds.

Macros are small programs that automate repetitive processes in Excel worksheets. You do not need to know how the macro works, just copy it to the Excel file where you want to use it and then run it. See below for detailed instructions.

Sub ClearAllNamesInWorkbook()
Dim Name As Name
For Each Name In ActiveWorkbook.Names
Name.Delete
Next
End Sub

<strong>How to Copy and Use a Macro</strong>
Firstly, select all the text of the macro (including the line at the beginning starting with the word Sub and the line at the end, End Sub) and press CTRL+C to copy.

Then Switch over to Excel and go to the Visual Basic Editor, ALT+F11. Press CTRL+R to open the Project Explorer, select your workbook file in the listing, right-click and choose Insert, Module.

Paste the macro into the module by pressing CTRL+V.

Finally, return to Excel (press ALT+F11 again) and run the macro by choosing Tools, Macro, Macros. Select the macro from the list and click the Run button over to the right hand side.

If you like the code and live in London and want to understand VBA code try a course in Excel VBA for everyone else try the VBA download manual
 

Excel time values only function within the 24 hour day; if you start work at 19:00 hours and end at 23:00 hours then Excel will calculate the elapsed time as 4 hours. Should you work beyond midnight, then the same calculation will produce a negative value as you would have stopped working at a time that was a lesser value than your starting time. The following function accepts any starting or ending time and calculates the actual elapsed time in hours.

Function macros are small programs that perform specialised calculations in Excel formulas. You do not need to know how the macro works, just copy it to the Excel file where you want to use it and then use it in a formula it. See below for detailed instructions.

Function SHIFTIME(Start_Time As Date, End_Time As Date)
'Accepts : Working shift start and end times.
'Returns : Number of hours worked as a decimal value.

If End_Time >= Start_Time Then
'Day Shift.
SHIFTIME = DateDiff("n", Start_Time, End_Time)
Else
'Night Shift; time to midnight plus time from midnight.
SHIFTIME = _
DateDiff("n", Start_Time, 1) + DateDiff("n", 0, End_Time)
End If
SHIFTIME = SHIFTIME / 60
End Function

<strong>How to Copy and Use this Macro</strong>

Firstly, select all the text of the macro (including the line at the beginning starting with the word Function and the line at the end, End Function) and press CTRL+C to copy.

Then switch over to Excel and go to the Visual Basic Editor, ALT+F11. Press CTRL+R to open the Project Explorer, select your workbook file in the listing, right-click and choose Insert, Module.

Paste the macro into the module by pressing CTRL+V.

Finally, return to Excel (press ALT+F11 again) and select the cell where you want to enter your
formula. Choose Insert, Function then go to the User-Defined category and choose the SHIFTIME function from the list. Fill-in the Start_Time and End_Time arguments with the relevant cell references on your worksheet.

If you like the code and live in London try one of our courses for everyone else download the free training manual EXCEL VBA
 

Excel Pivot Tables do not automatically update when the source data is changed, they have to be refreshed. To make your Pivot Tables refresh automatically you need a macro to do the refreshing and an Event to trigger the macro. This seems quite an involved process the first time that you do it but it gets easier, it is mainly just copying from one workspace to another.

Recording the Macro
You have to record a macro and then copy the recorded code from the macro into one of the worksheet’s events. Firstly, the recording: Select a cell in your Pivot Table and then choose Tools, Macro, Record New Macro from the menu. Just make sure that the Store macro in: setting is This Workbook and click the OK button.

Now you have to record your refreshing the Pivot Table, so choose the Data menu and select Refresh Data. The recording is completed and you can turn off the Macro Recorder by choosing Tools, Macro, Stop Recording. The next job is to find the recorded code and copy the instruction that refreshes the Pivot Table. Choose Tools, Macro, Macros and select the recorded macro from the list and then click the Edit button on the right hand side. You will now see the Visual Basic Editor displayed and you can view the recorded procedure, which should look something like this:

Sub Macro1()
' Macro1 Macro
' Macro recorded by Anon E. Mouse
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
End Sub

Copy the line of code that refreshes the table, it should be obvious which one you need. Do not copy anything else. Now, return to the Excel worksheet workspace by clicking an Excel icon or by pressing ALT-F11.

Using an Event to trigger the Macro
Next, you need to find the Event code shells for the worksheet where your Pivot table is located. Right-click the worksheet tab for the relevant worksheet and choose View Code from the shortcut menu. You are returned to the Visual Basic Editor once again. Looking at the active window you will see two drop-down lists at the top of the window.

The drop-down list on the left hand side is the Object list, the one on the right hand side is the Procedure list. Drop the Object list and select the Worksheet object, then choose a suitable event from the Procedure list. The Activate event is probably the most suitable for our purposes. Paste your recording into the procedure so that it looks like this:

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub

The Activate event is triggered whenever you make the worksheet active, so in order to test that the procedure is working correctly you need to perform the event. Return to the Excel worksheet workspace by clicking an Excel icon or by pressing ALT-F11, select another worksheet in the workbook and then select the original worksheet where the Pivot Tables are stored. This triggers the event and you will see that the tables are automatically refreshed. Of course, you do not have to make the initial macro recording every time that you need to do this, all you need is the code for the Refresh method so you can type it in or paste it from a previous recording. But, be careful, each Pivot Table needs to be correctly described using its Name Property; this is the text value inside the brackets. In the example below the name of the Pivot Table is "PivotTable1"

ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh

To find out the name of a specific Pivot Table, right-click one of its cells and choose Table Options from the short cut menu. The name is shown in the top left corner of the dialog. It is quite in order to list the Pivot Tables if you have more than one which needs refreshing in which case the procedure would be like this:

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
End Sub

Although it would be easier to have a looping procedure so that you can avoid having to look up the name of each individual Pivot Table:

Private Sub Worksheet_Activate()
Dim pt As PivotTable
For Each pt In ActiveSheet.PivotTables
pt.PivotCache.Refresh
Next
End Sub

Pivot Tables are contained by each individual worksheet so if you want to have a procedure which will refresh every Pivot Table on every worksheet in the entire workbook then you will need an outer loop for the worksheets and an inner loop to refresh the pivot tables. The code would be as follows:

Private Sub Worksheet_Activate()
Dim ws As Worksheet
Dim pt As PivotTable
For Each ws In Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.Refresh
Next pt
Next ws
End Sub

If you like the code and live in London try one of our Excel VBA Classes if not they download our manual on Excel VBA

If you are a help desk specialist trying to help a remote user with Ms Word and you can’t see their screen I have a tip for you. In all versions of Word you can type

 The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the lazy dog.

The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the lazy dog.

The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the lazy dog.  The quick brown fox jumps over the lazy dog.

 To get sample text. To control the number of paragraphs you can use two arguments

 =rand(Paragraphs,Sentences)

ie, =rand(2,8) will give you two paragraphs and eight sentences.

 Additionally in Word 2007 there is an alternative syntax which gives you sample latin

    =lorem(paragraphs,sentences)

This is useful for mocking up brochure layouts with sample text

 In Excel there is a similar function called

 =Rand() which will produce a random number between 0 and 1.

=Int(rand()*49) will produce a whole number between 1 and 49.

What would be really useful would be a sample record generator for Access to produce records for predefined fields. Does anyone know of such a tool?

 Anyway for more information on Word or Excel download our free training materials

MS Project is an extremely powerful package containing a myriad of tools to enable project managers to successfully plan, manage and track complex projects. However, Project can also be extremely useful even if you only use a fraction of its capabilities. Whatever your level of project experience, good quality MS Project training will help you to get the most from MS Project and avoid the pitfalls that can trap the unwary (or inexperienced).

One of the most important elements of achieving a successful outcome for your project is the planning phase. Without proper and thoughtful planning you are doomed to an expensive endeavour, most likely ending in failure. By identifying dependencies, issues and risks before the project has started, you can take mitigating steps to save you both time and money.

Arguably, it is during the planning phase is where MS Project is most valuable. By setting out your tasks to create a Gantt chart you are forced to think about what you intend to do. It is during this process that you may identify a step or process that you had previously escaped attention. You will need to consider how tasks relate to one another; what must you do before a particular task can be started (or finished, in some cases)?

It is about this point that it is harder to progress without MS Project training. After all, the task list can be typed or copied and pasted from Excel without too much difficulty. But how do I specify the start date of the project? Why do all my tasks start from the same day? How do I link tasks, let alone ensure that they have the correct type of dependency?

The answers to these questions can be discovered without training but how long will that take? (How much is your time worth, by the way?) There are also questions to be considered that may not be immediately obvious to a self taught user. What happens if the project start is delayed? Do the default working hours for MS Project match the working hours for your organisation? If you have entered the basic information correctly, you can easily adapt, modify and analyse your plan. If you want to know how much your plan will cost, specify the costs for your resources and use the in-built reports to view the figures.

Most Introduction to Project courses take two days to complete. This is not because the content is especially difficult to learn, but because there is so much to learn. Starting from the basics of setting up the project, managing calendars and working times, setting task durations, dependencies and constraints, adding resources and tracking the progress of a project, the time and expense of training will be repaid in productivity.

Beginners come to training with a very limited expectation of what MS Project can give them. Most will come away with a whole new appreciation of a package that, with good training, supported by a good MS Project manual, can make their lives so much easier.

At Mouse Training we offer both quality Project training and free MS Project manuals. If you are based in London, why not consider one of our courses? If you are not London-based, our Project manuals are downloadable anyway no charge
 

Modern managers are routinely expected to understand the principles of project management.

Once a project has been initiated it is normally followed by a planning phase. This is in turn followed by executing the planned tasks on schedule until the final completion date. A successful Project Manager must manage key aspects of a project: resources (people, materials), time, money and scope.

 Project management harnesses the effective management of all resources needed for the completion of the project. These include people, equipment and all materials required for project completion. Microsoft Project allows management to define key resources required for the completion of tasks and then constantly review the efficiency of their initial allocation of resources through the lifecycle of the project.

 Projects can be reduced to list of tasks which need to be executed in conjunction with estimates of their completion time. Microsoft Project allows the project manager to design Gantt charts to monitor their progress.

Completing projects within or on budget is a key function of project management. MS Project facilitates the project manager to design a realistic budget for their project and calculate actual costs as the project progresses.

The project should be clearly defined from the start. The initial project scope may be adjusted by events which are likely to impact on the allocation of resources, time scale and budget.

MS Project is a tool to help you to plan projects, manage and update project information, and communicate the status once the project is under way.

The details of the project tasks and associated resources are entered into the system as a new project. The system will then display the data in such a way that the relationships of the tasks and their time scales can clearly be seen and potential problem areas identified.

Project data can be entered and/or viewed in a number of ways; the three principal formats are charts, forms, and sheets.

Charts can be either Gantt Charts or Network Diagram Charts both of which are a diagrammatic representation of the project data.

At the heart of every project management system is a scheduling algorithm. An algorithm is a mathematical or logical equation that solves a complex problem by breaking down the problem into simple steps. When scheduling resources and parameters are entered into it, the scheduling algorithm produces a project schedule that would be impossible for you to produce manually.

In Microsoft Project, however complex your project may be, you can vary only information regarding tasks or resources. The information you provide is fed into the “Black Box” or algorithm, to provide you with a schedule in the form of a Gantt chart, Network Diagram Chart, or Resource Graph. In summary, the seven or eight parameters that you enter result in output that is a schedule displayed on various views and forms.

The default Project view is the Gantt chart view, as displayed below. This view is used extensively in Microsoft Project. The Gantt chart consists of a Gantt table and a Gantt bar chart. The divider bar separates the two and can be repositioned to display more of the table or more of the chart. The Gantt table consists of rows and columns. Just like on a spreadsheet, the intersection of a row and a column is called a cell. The Gantt bar chart graphically displays your schedule on a time line.

Allocation of resources becomes easier when tasks can be listed, sub divided and quantified.  The ability to Track tasks means managers can see immediately how a missed deadline on one task might negatively impact the overall project completion date. Delays in some tasks might have negligible effect on the schedule while others might be deemed critical and significantly undermine completion of the overall schedule.

The Gantt chart is the central scheduling feature of MS Project

Its advantage lies in the fact that it is a graphical illustration of a project schedule that assists with the planning, coordination, and tracking of specific tasks within a project.

A Gantt chart plans the tasks that need to be completed, sets a timeline for the tasks, plans the allocation of resources and creates critical paths for tasks.

Gantt Charts identify all activities to be completed. The chart lists of all the tasks that need to be completed during with an estimated expectation of their completion. The Gantt chart then needs estimates for each of the tasks in the project plan. At this stage the plan can then have resources allocated. A levelling feature can ascertain that resources are available when needed for a task and not being used for another task.

A Gantt chart can be used to keep track of progress for each activity and how the costs are running. Gantt charts guide the planning phases of the project and facilitate the monitoring of the project to ensure it stays on track

The project manager should examine the duties of each resource defining the steps to create an overall project schedule for each resource. The manager will also need to assign time estimates to each step in the process and integrate all task lists into a calendar.

Project transcends scheduling people's time; it's scheduling resources. Although time is an important resource, others include raw materials, external organizations, and costs.

Assigning resources to tasks and employees allows the manager to predict and prepare a budget for the project. Tracking resource usage allows the project leaders to adjust the budget as required. Workloads can then be adjusted to meet individual and final milestones

If a project is poorly scoped from the outset and quality and risk management principles are not planned the project could fail.

The project must be defined properly if it is going to be successful. Schedules are essential for to provide an indication of progress, and for checking the work remaining to be completed

All projects should be effectively managed, from the initial planning process to the final milestone. This includes managing problems associated with slipping tasks within a project plan.

ll aspiring project managers should be wary of the tendency of a project to include more tasks than originally specified, which often leads to higher than expected costs and a missed final milestone

You may want to evaluate the success of a project. To decide if the project was successful you need to consider if the project completed within budget, on time and to agree quality standards.

Now you just need to consider how you are going to learn all the new features. Is it going to be www.mousetraining.co.uk/ms-training/microsoft-project-training-courses.htmlMs Project books, online tutorials or back to the classroom?

The sheer scale of Excel 2007 is amazing boasting over a million rows and sixteen thousand columns.

New Ribbons display more of the menu choices than in the older menu driven systems of Excel 2003. There are of course new features to including a range of smart art options. Conditional formatting is now much easier with lots of coloured flags and traffic lights to choose from. The database functionality has been given a thorough revamp allowing the user to filter by a wide range of criteria including easier use of date options. A great new feature allows you to delete duplicate records in a dataset.

The majority of Excel users probably never get beyond the basics of arithmetic and using auto sum but the 2007 interface makes it much easier to create complex workbooks that can be shared with colleagues

Apart from added functionality you can easily add table styles to give your spreadsheets a professional look and feel.

Don’t neglect the enhanced Pivot table functionality that presents your data in a summary format

Pivot tables take large amounts of data and summarise it. They are flexible in the way they present it and what information they are delivering. We all require efficient summarization of information served up in a palatable format. Pivot tables are capable of being customized

You may still be wondering if there are still compelling reasons to upgrade from an earlier version if your data is optimised for the earlier version. Do you really want to have to retrain your staff and test all our key documents in the new version?

You need to consider whether the new version adds significant functionality to justify the upgrade. You need to be sure you or your colleagues will use the new features.

Now you just need to consider how you are going to learn all the new features. Is it going to be books, online tutorials or back to the classroom.

The key purpose underlying a PowerPoint presentation is the chance to persuade an audience of a concept, product or service.

To begin your slide presentation the presenter should focus on your key message and construct their presentation around this topic. Select a topic that illustrates your key message. Your PowerPoint presentation will also require structure to retain your audience’s interest throughout the proceedings.

The opening slides of a presentation should be aimed at capturing the attention of your audience. Ensure you have a good balance of text, pictures, conceptual diagrams, tables and graphs to keep your audiences interest once you have established their attention with your opening gambit

Ensure you present your audience with the right business tone to match your presentations aims and objectives. Be enthusiastic about your subject matter. Be calm and confident delivering your message which should be presented in a logical order at a pace that does not alienate your target audience.

To avoid your audience having to write copious notes and pay rapt attention to the message. Reassure attendees that a printed copy will be readily available at the end of the session. Don’t make the mistake of handing your presentation out at the beginning otherwise people will flip ahead or worst case leave half way through.

With respect to the detail on the slides keep the animation to a absolute minimum. Too much animation will obscure your message rather than enhance it. Graphics are effective tools, often more effective than words on PowerPoint slides. Don’t put to much text on the slides or you may be tempted just to just read out the presentation. Poor use of Color can also detract from your message. Ensure the font and background colors who select contrast adequately to make your results clearly legible.

Overuse of Clip art or background photographs can also distract your audience away from the key message you are trying to convey.

 The key goal of your PowerPoint presentation is to convey a message. The presenter needs to use methods that emphasize the key points of your message

 Many presenters only take questions until the end of their presentation. Interruption can upset the speaker. However audience can enhance the presentation if the speaker is confident in their subject matter and the PowerPoint presentation can be delivered in both a linear and non linear fashion

PowerPoint allows the presenter to vary their delivery according to audience reaction. The creation of Custom shows gives the possibility of changing the sequence or slide order and tailoring the outcome to suit the audience. You might not choose to show all slides to all audiences. If there is a specific slide that you do not want your audience to view, you can hide this slide. If this is a frequent requirement of yours custom views allows you to generate variable content for different audiences.

Don’t forget the power of the Slide Master. This allows you to create a template designating the typeface, placeholder, and background design and color schemes. If this facilitates altering all your slides in a presentation which will then be applied throughout every slide. You can use this feature to create a consistent look and feel. Put the company logo on the slide master and then it will appear on every page, in exactly the same position and it will be the same size. This will help prevent the inconsistency that occurs when different fonts, colors and sizes appear on each slide.

 Learn how to use PowerPoint’s navigation tools effectively. Sometimes one of your audiences may ask to see the previous slide again. Make sure you know the best way to navigate back and forth without it looking messy.
Do Not Read Your Slides
your slides are an aid memoire for your real content. The bullets are simply there as reminders for topics on which you will enlarge.
Face Your Audience!
Don’t look at the screen when talking. Look at the audience.
Don’t Rely on Removable Media
if your presentation is on a CD, USB or other form of removable media copy it onto the hard drive of the system you will be presenting for. This will cut down the margin of error.

Know your venue. If your venue is offering light pens, dimming options on lighting and a wireless Mouse check then all work and rehearse how to use them. Better still make sure that you have the number of the support person if it all goes wrong. Have a plan B. If the projector blub blows have paper copies or transparencies at hand.

If you don’t feel confident you can do this by yourself download our free training manuals from our web site. If you are in London you might want to think about a classroom course in Powerpoint (Training)

Creating an effective presentation is really simple if you know how to use PowerPoint. With our tailored approach to creating all our PowerPoint courses, you are sure to gain the skills needed.

Many users already have a basic understanding of applications, and why should you then waste valuable time on topics you already know? What makes us different is that we tailor all our courses. We spend the time and effort to ensure you get the most from your PowerPoint course and a sound return on your investment.

We are a London based PowerPoint training course provider, offering our clients training on PowerPoint, either onsite or at our London city based training center.

Our PowerPoint training courses can be tailored to suit your exact needs. We also offer a free Training Needs Analysis to ensure you are are receiving the most from your PowerPoint training course. As one of the best PowerPoint training providers in London, we pride ourself on our reputation, our dedication and customer service.

I am sure that any user of Excel will figure out the interface and how to create basic arithmetic calculations involving + - * /. Probably they will make sense of the five most used functions on a spreadsheet SUM, MIN, MAX, AVERAGE and COUNT. Probably they would be able to format the spreadsheet and print it. (basic spreadsheet training)

However, it is my contention that dealing with absolute cell addressing and calculations involving multiple sheets or files would need training in person.

Attending a training course would optimize learning more sophisticated options in a structured and ordered way avoiding random and adhoc sampling of other modes of learning.

An Excel advanced course allows the trainee to focus on each topic in a structured manner. A well organized syllabus gives the client a connected environment to learn how advanced functions work together.