Data Mobility with Cut Copy and Paste

Some of the most mind-numbing work I can think of is performing a long, manual, repetitive sequence of copy/paste or cut/paste. Have you ever experienced this? Maybe you receive a dump of data from some other system and need to systematically sort and group the data and then prepare a report for each grouping. Based on my observations, I'm not the only person who has suffered through this kind of activity. More than a few people have probably become attracted to the potential benefits of automation with VBA while enduring the mental pain associated with this activity.

If you know of a process or two that include large amounts of shuffling data around, automating these processes could be your first big win as an Excel developer. Usually it's fairly easy to automate these processes, and the time savings of automation versus doing it manually can be monumental.

To complete such a task, however, you need to learn how to use the Cut, Copy, and PasteSpecial methods of the Range object. Cut and Copy can be used identically. Use Cut when you want to move the range to a new location and remove any trace of it from its original location. Use Copy when you want to place a copy of the range in a new location while leaving the original range intact.

YourRangeObject.Cut [Destination] YourRangeObject.Copy [Destination]

The optional Destination parameter represents the range that should receive the copied or cut range. If you don't supply the Destination parameter, the range is cut or copied to the Clipboard.

NOTE An example of Copy is shown in the CopyItem procedure shown in Listing 9.1.

If you use Cut or Copy without specifying a destination, Excel will still be in cut/copy mode when your procedure finishes. You can tell when Excel is in cut/copy mode by the presence of a moving, dashed border around the range that has been copied (see Figure 9.1).

When I first started developing, it took me forever to figure out how to turn cut/copy mode off; when I did figure it out, I didn't even do it correctly. I used the SendKeys function to send the equivalent of the Escape keystroke to the active window. It did the job, but it wasn't very elegant. Anyway, the Application object has a property called CutCopyMode. You can turn cut/copy mode off by setting this property to false.

' Turn cut/copy mode off

Application.CutCopyMode = False

You can use PasteSpecial to copy the contents of the Clipboard to a range. PasteSpecial is quite flexible regarding how the contents of the Clipboard get pasted to the range. You can tell it to paste everything, comments only, formats only, formulas only, or values, among other things.

YourDestinationRange.PasteSpecial [Paste As XlPasteType], _ [Operation As XlPasteSpecialOperation], _ [SkipBlanks], [Transpose]

All of the parameters of PasteSpecial (listed momentarily) are optional. If you don't specify any of the parameters, the contents of the Clipboard will be pasted to the range as if you had used the Copy method.

Paste You can use the Paste parameter to specify what gets pasted to the range. You can specify one of the xlPasteType constants: xlPasteAll (default), xlPasteAllExceptBorders, xlPasteColumnWidths, xlPasteComments, xlPasteFormats, xlPasteFormulas, xlPasteFormulasAndNumberFormats, xlPaste-Validation, xlPasteValues, and xlPasteValuesAndNumberFormats. I'll assume that you can figure out what each of these constants does.

Figure 9.1

The dashed border around this range indicates that Excel is in cut/copy mode.

Figure 9.1

The dashed border around this range indicates that Excel is in cut/copy mode.

Microsoft UÏFice :>(•'' /JH., Beta - tfook^


ill] File Edit Insert Format

Tools Data

! Window tefc

_ IÏ X 1

UBUjjio fg.5 51 ' JSi


Ai J


:uj -3ia.>t ijj

i äi iu


A2 ~ f.

A I B 1 C


: ET"



1__! 1










M *

► M \ Sheet 1 / Sheet2 / She I <


> J"

i 1

Operation The Operation parameter specifies whether to perform any special actions to the paste values in conjunction with any values that already occupy the range. You can use one of the xlPasteSpecialOperation constants: xlPasteSpecialOperationAdd, xlPasteSpecialOperationDi-vide, xlPasteSpecialOperationMultiply, xlPasteSpecialOperationNone (default), and xlPasteSpe-cialOperationSubstract.

SkipBlanks This Boolean (true/false) parameter specifies whether or not to ignore any blank cells on the Clipboard when pasting. By default this parameter is false.

Transpose Transpose is a slick, little-known (by Excel users in general anyway) feature. Like its regular Excel counterpart, this optional Boolean parameter transposes rows and columns. If you have a series of values oriented horizontally, and you paste it using the value true for the Transpose parameter, you'll end up with a series of values oriented vertically.

I suppose this would also be a good place to mention the Delete method. Delete has one optional parameter—Shift—that represents how remaining cells on the worksheet should be shifted to fill the void left by the deleted cells. You can use one of the defined xlDeleteShiftDirection constants: xlShiftToLeft or xlShiftUp.

0 0

Post a comment