Custom Paste Values In Cells

You can customize how values paste into a worksheet from the Windows Clipboard by using the PasteSpecial method. You can use the PasteSpecial method with values that have been added to the Windows Clipboard using the Cut or Copy methods, or even values placed there directly from Excel. With the PasteSpecial method, you can customize how the cell contents are pasted into the new range by only pasting the cell formats or even by adding the cell values to the contents of the cells from where you are pasting. The PasteSpecial method is essentially the same as using EditOPasteSpecial in Excel.

Typically when you use the Cut or Copy method, you indicate where to place the cell values and Excel places the values in that location instead of in the clipboard. If you plan to use the PasteSpecial method with a Cut or Copy method, you should not use the Destination parameter with either method.

The PasteSpecial method has four different optional parameters: Paste, Operation, SkipBlanks, and Transpose.

The Paste parameter indicates how you want to paste the information into the new range. By default, Excel uses the xlPasteAll constant value for this parameter, which pastes the entire contents of the copied or cut cells into the new range.

The Operation parameter enables you to perform a mathematical operation, such as adding the current value of a cell to the pasted value. The default constant value used by Excel is xlPasteSpecialOperationNone, which does not perform any mathematical operatons.

Set the SkipBlanks parameter to True to ignore blank cells in the clipboard and avoid having them pasted into the new cells. If selected, existing values remain in cells that would have received blank values.

If you want to transpose the data values from rows to columns or vice versa, specify a value of True for the Transpose parameter.

CUSTOM PASTE VALUES IN CELLS

CUSTOM PASTE VALUES IN CELLS

'-H Create a new subrouti

0 Type For N = 2 To 6, replacing 2 and 6 with the numbers corresponding to the columns to add.

-0 Type Range(Cells(3,N),Cells(16,N)).Copy, replacing Cells(3,N),Cells(16,N) with the range to copy.

'-H Create a new subrouti

Note: See Chapter 3 for information on creating subroutines.

0 Type For N = 2 To 6, replacing 2 and 6 with the numbers corresponding to the columns to add.

-0 Type Range(Cells(3,N),Cells(16,N)).Copy, replacing Cells(3,N),Cells(16,N) with the range to copy.

0 0

Post a comment