Moving from Microsoft Office VBA to AppleScript:
MacTech's Guide to Making the Transition

Page Prev and Page Next buttons at bottom of the page.

April, 2007
Page 59

     --get the value in the clipboard to a cell in a temporary worksheet

     tell (make new worksheet at active workbook)

          tell cell 1 of row 1

              set value to 2

              copy range -- to clipboard

          end tell

     end tell

     paste special (range "A1:J10" of sheet "Sheet1") what paste all ¬

          operation paste special operation multiply

     --delete the temporary worksheet

     set display alerts to false

     delete active sheet

     set display alerts to true

     set screen updating to true

end tell

And quite impressive it is too. Take a good look at the dictionary for paste special and the various enumerations of its parameters. Occasionally the names of keywords vary from the VBA ones, for example what rather than Paste. This is always to avoid terminology conflicts in AppleScript where keywords have a broad scope: paste is a very common term in scripting additions.

Transpose a Range

You can also use the paste special command method to transpose columns and rows. Note that, if the destination range has more than one cell, it must have the same number of rows and columns as the source range has columns and rows:

With ActiveSheet

   .Range("A1:E2").Copy

   .Range("A4:B8").PasteSpecial Transpose:=True

End With

becomes:

tell application "Microsoft Excel"

     copy range range "A1:E2"

     paste special range "A4:B8" with transpose

end tell

Note that paste special will not allow you to transpose the range in place, even if you‘re using a square range. Also recall that compiling transpose true compiles to with transpose.

Inserting a Range

You can insert blank cells into a range using the insert into range command (equivalent to the VBA Insert method), as long as there is room to do so without moving populated cells off the worksheet. It is just like the Insert/Rows-Columns-Cells menu in the UI.

Sheets("Sheet1").Range("B2:B5").Insert Shift:=xlShiftToRight

In AppleScript:

insert into range range "B2:B5" shift shift to right

If a range has been copied, and the Insert method applies to a range of the same size, the clipboard contents will be copied to the inserted cell.

With ActiveWorkbook

   With Sheets("Sheet2").Range("A1:A10")

   .Copy

   Sheets("Sheet1").Range("B2").Resize( _

      .rows.count, .columns.count).Insert _