Microsoft Office Tutorials and References
In Depth Information
Try It
Try iT
In this lesson, you practice creating a recorded macro.
lesson requirements
For this lesson, you create a macro by turning on the Macro Recorder, copying a range of
formulacontaining cells, and using Paste Special to convert the formulas in that range to values.
step-by-step
Start by establishing a situation where you have dynamic formulas in cells where you prefer to show
static values. In a fresh worksheet, select cell A1:D10, and type the formula =INT(RAND()*1000) .
Press Ctrl+Enter, which will insert formulas in A1:D10 that return a random number between 0 and
199. Select any single cell to deselect range A1:D10.
Create a macro that copies the range of dynamic RAND numbers, and paste the numbers over the
range as values to obtain static numbers:
1.
Turn on the Macro Recorder by clicking the Record Macro button.
2.
In the Record Macro dialog box, name the macro ValuesOnly and assign it the shortcut
Ctrl+Shift+C.
3.
Click OK to start recording your ValuesOnly macro.
4.
Select range A1:D10.
5.
Press Ctrl+C to copy the selected range.
6.
Right-click within the selected range.
7.
Left-click Paste Special, select Values, and click OK.
8.
Press the Esc key to exit copy mode.
9.
Click cell A1 to deselect all cells except A1.
10.
Turn off the Macro Recorder by clicking the Stop Recording button.
11.
Re-enter formulas in range A1:D10 and test your macro with the shortcut Ctrl+Shift+C, or
by displaying the Macro dialog box, selecting the ValuesOnly macro name in the list, and
clicking the Run button.
Once you run your macro, the formulas you entered will now be hard numbers.
To get the sample database files, you can download Lesson 2 from the book’s website at
www.wrox.com .
To view the video that accompanies this lesson, please select Lesson 2, available at
the following website: www.wrox.com/go/excelvba .
 
Search JabSto ::




Custom Search