Macros : Manipulating Worksheets

macro_flash_tube_prototype

hmmm….. not that type of macro…

In macros, the Worksheets object allows you to manipulate individual worksheets inside a workbook. This can be advantageous when you are doing things like running a macro to prepare a management briefing book which might be spun out via a customised batch printing process to Excel. You can take existing sheets and add new, delete , rename as required.

Here are a few examples:

Example 1: Returning the name of the current worksheet

$value = ActiveSheet()->name();
return __msgbox($value, 'Worksheet Name', 'Info');

Example 2: Renaming the current sheet, and display the value in a message box:

$RenameWorksheet = ActiveSheet()->Range('E10')->value;
$value = ActiveSheet()->name($RenameWorksheet);
return __msgbox($RenameWorksheet, 'Renamed Worksheet', 'Info');

Example 3: Adding a new worksheet to the workbook, and display the value in a message box:

$AddWorksheetName = ActiveSheet()->Range('E13')->value;
$value = activeworkbook()->WorkSheets()->add($AddWorksheetName);
return __msgbox($AddWorksheetName, 'New Worksheet Added', 'Info');

Example 4: Count the worksheets in the workbook, and display the value in a message box:

$value = activeworkbook()->WorkSheets()->count();
return __msgbox($value, 'Worksheet Count', 'Info');

You can download a copy of the above here

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s