since 1999
Please watch the video above to see how to use PATools Macros.
Below is the full list of available commands. More will be added over time - let us know if you have any requests.
Columns 1 and 2 are not case sensitive.
| Entry | Explanation |
| {BLANK} | Ignore line |
| ;{ANYTHING} | Start a comment / ignore whats on this line |
| SEL or . (Dot) | Selected range |
| RGN | The used region of cells around the current selection |
| NamedRange | A named range |
| a1 | Cell A1 |
| a2:b10 | Range A2:B10 |
| B15,B23,B24 | Cells B15, B23 & B24 |
| [CR] | Current Row eg if A2 is selected then D[CR] will be cell D2 |
| [CC] | Current Column eg if D2 is selected then [CC]8 will be cell D8 |
| [LR] | Last Row used in the column eg if range D2:D50 is used then D[LR] will be cell D50 |
| [LC] | Last Column used in the row eg if range D2:H2 is used then [LC]8 will be cell H8 |
| [FR] | Final Row used in the whole worksheet eg if various ranges are used and the final cell on the sheet is P150 then D[FR] will be cell D150 (and [FC][FR] will be cell P150) |
| [FC] | Final Column used in the whole worksheet eg if various ranges are used and the final cell on the sheet is P150 then [FC]8 will be cell P8 (and [FC][FR] will be cell P150) |
| UP 1 | Move selected range up 1 eg D4 will become D3, and D3:H12 will become D2:H11. Of course 1 in this example could be any number. |
| DOWN 1 | Move selected range down 1 eg D4 will become D5, and D3:H12 will become D4:H13. Of course 1 in this example could be any number. |
| LEFT 1 | Move selected range left 1 eg D4 will become C4, and D3:H12 will become C3:G12. Of course 1 in this example could be any number. |
| RIGHT 1 | Move selected range right 1 eg D4 will become E4, and D3:H12 will become E3:I12. Of course 1 in this example could be any number. |
| GODOWN 1 | Move selected range down 1 ignoring hidden rows eg D4 will become D6 if D5 is hidden / filtered out. Use with care on ranges of more than 1 cell. Of course 1 in this example could be any number. |
| END D | Go to last cell in the range downwards eg if range C4:C20 is used and we are on C9, then C20 will be selected; if C1:C3 are blank and we are on C2 then C4 will be selected. |
| END U | Go to last cell in the range upwards eg if range C4:C20 is used and we are on C9, then C4 will be selected; if C21:C30 are blank and we are on C25 then C20 will be selected. |
| END R | Go to last cell in the range to the right eg if range C4:Y4 is used and we are on H4, then Y4 will be selected; if A4:B4 are blank and we are on A4 then C4 will be selected. |
| END L | Go to last cell in the range to the left eg if range C4:Y4 is used and we are on H4, then C4 will be selected; if Z4:AC4 are blank and we are on AA4 then Y4 will be selected. |
| Flow Commands | |
| COUNTER x y | Start a counter; first use will be value x with subsequent uses going up by y. |
IF | Run all the following commands up to IF/ if the sum of the current range matches the criteria: {ANY OF} <x >x <=x >=x =x <>x (where x is a value) | |
| IF/ | Marks the end of the commands to be run subject to the IF statement. |
| RPT x | Run all the following commands up to RPT/ x number of times. |
| RPT/ | Marks the end of the commands to be run multiple times. |
Columns 3 and 4 are left blank or used for parameters as below.
| Entry | Parameters | Parameters | Explanation |
| {BLANK} | No operation to perform ie simply select the range specified in Column 1. | ||
| ;{ANYTHING} | Start a comment / ignore this operation. | ||
| ENTER | {TEXT/NUMBER/FORMULA} OR [PROMPT] OR [COUNTER] OR [RANDOM] | Enters data into each cell in your range. Can be text, number of a formula. Also use [Prompt] to prompt for input whilst running the macro. Use [Counter] to enter the counter value. Use [RANDOM] for a random number between 1 and 99. These last three can be within other content eg Region [COUNTER]. | |
| VAL+ | 2 | Convert the value of each cell by adding the specified value; 2 in this example. | |
| VAL- | 2 | Convert the value of each cell by subtracting the specified value; 2 in this example. | |
| VAL* | 2 | Convert the value of each cell by multiplying by the specified value; 2 in this example. | |
| VAL/ | 2 | Convert the value of each cell by dividing by the specified value; 2 in this example. | |
| VAL | Convert a formula to the value for each cell in the range ie same as doing Copy & PasteValues. | ||
| CALCULATE | Forces a calculation of the workbook (useful if in Manual Calc mode). | ||
| INS | {Blank} OR {ANY OF} RC | Insert cells in your range. Specify R for Rows or C for Columns. | |
| DEL | {Blank} OR {ANY OF} RC | Delete cells in your range. Specify R for Rows or C for Columns. | |
| CLEAR | {Blank} OR {ANY OF} CFNMH | Clear your range. Specify C for Contents, F for Formats, N for Notes, M for Comments, H for Hyperlinks. | |
| COPY | Copy your range to the clipboard. | ||
| CUT | Cut your range to the clipboard. | ||
| PASTE | {Blank} OR {ANY OF} VF | Paste the Clipboard. Specify V for Values Only, F for Formats Only. | |
| FONT= | {ANY OF} BIUD | Specify B for Bold, I for Italics, U for Underline, D for Double Underline, for all cells in the range. | |
| FONT<> | {ANY OF} BIUD | Specify B for NOT Bold, I for NOT Italics, U for NOT Underline, D for NOT Double Underline, for all cells in the range. | |
| FONTCOLOR | {ANY OF} DRBGKW OR {COLORINDEXNO} | Sets the font color for all cells in the range. Specify the colorindex value, or D for Default, R for Red, B for Blue, G for Green, K for Black, W for White. | |
| FILLCOLOR | {ANY OF} DRBGKW OR {COLORINDEXNO} | Sets the fill color for all cells in the range. Specify the colorindex value, or D for Default, R for Red, B for Blue, G for Green, K for Black, W for White. | |
| NUMFORMAT | {NUMBER FORMAT} | Specify the numeric format for all cells in the range. | |
| WRAP | Applies WrapText to each cell in the range. | ||
| AUTOWIDTH | Applies Autowidth to each column in the range. | ||
| AUTOHEIGHT | Applies Autoheight to each row in the range. | ||
| SORT | {Blank} OR D | Run a sort on your specified range. Specify D for Descending values. | |
| FILTER | Turns the Filter on (or off if already on). | ||
| FIND | WHAT | {Blank} OR {ANY OF} VWC | Find within the specified range. Specify V to look in Values (normally Formulas are searched), W for Whole of cell (normally it is any part), C to match Case (normally it is not case sensitive). |
| REPLACE | FIND | REPLACE | Run a search and replace in your range. |
| NAME | NAME or {Blank} | Names your range as specified. Specify no name and any current name will be deleted. | |
| SHEETCOPY | {Blank} or {NewName} | Make a copy of the worksheet. Specify the name for the new sheet if you wish. | |
| SHEETSEL | SHEET TO SELECT | Specify which sheet to work on. | |
| SHEETRENAME | {Newname} | Renames the worksheet. | |
| SHEETNEW | {Blank} or {NewName} | Insert a new worksheet. | |
| SHEETPROTECT | PASSWORD or {Blank} | Protects the worksheet (optional password) | |
| SHEETUNPROTECT | PASSWORD or {Blank} | UnProtects the worksheet (optional password) | |
| SAVEACOPY | PATH AND FILENAME | Save a copy of the spreadsheet to the specified path and filename. | |
| {Blank = 1} OR {NO OF COPIES} | Print the spreadsheet (not the range). Specify the number of copies if more than 1. |