Microsoft Excel - Paste - Asked By Dan on 12-Sep-11 10:43 AM

I do I paste data into an activecell? i have tried  Cells(ActiveCell.Paste) = data  this but get an error.

    ActiveCell.Offset(1, 0).Select    '  goes to the cell below and should paste
    Cells(ActiveCell.Paste) = Data    '  I get an error
Pichart Y. replied to Dan on 12-Sep-11 10:50 AM
Hi Dan, 

This is the correct one...

ActiveCell.Offset(1, 0).value=data

while data is defined variable.

pichart Y.

Radhika roy replied to Dan on 12-Sep-11 11:52 AM

Move or copy cells and cell contents
Using the CutCopy, and Paste commands in Microsoft Excel, you can move or copy entire cells or their contents. You can also copy specific contents or attributes from the cells. For example, you can copy the resulting value of a formula without copying the formula itself, or you can copy only the formula.
Excel displays an animated moving border around cells that have been cut or copied. To cancel a moving border, press ESC.

Move or copy entire cells

When you move or copy a cell, Excel moves or copies the entire cell, including formulas and their resulting values, comments, and cell formats.

  1. Select the cells that you want to move or copy.

How to select cells

A single cell Click the cell, or press the arrow keys to move to the cell.
range of cells Click the first cell of the range, and then drag to the last cell.
A large range of cells Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can scroll to make the last cell visible.
All cells on a worksheet

Click the Select All button.

Select All button

Nonadjacent cells or cell ranges Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
More or fewer cells than the active selection Hold down SHIFT while you click the last cell that you want to include in the new selection. The rectangular range between the active cell and the cell that you click becomes the new selection.

 NOTE   To cancel a selection of cells, click any cell on the worksheet.

  1. Do one of the following:
    • To move cells, click Cut Button image on the Standard toolbar, or press CTRL+X.
    • To copy cells, click Copy Button image on the Standard toolbar, or press CTRL+C.
  2. Select the upper-left cell of the paste area.

Tip  To move or copy a selection to a different worksheet or workbook, click another worksheet tab or switch to another workbook, and then select the upper-left cell of the paste area.

  1. Click Paste Button image on the Standard toolbar, or press CTRL+V.


  • To choose specific options when you paste cells, you can click the arrow next to Paste Button image on the Standard toolbar, and then click the option that you want.
  • By default, Excel displays the Paste Options button on the worksheet to provide you with special options when you paste cells, such as Keep Source Formatting and Match Destination Formatting. If you don't want to display this button every time you paste cells, you can turn this option off. On the Tools menu, click Options. On the Edit tab, clear the Show Paste Options buttons check box.
  • Excel replaces existing data in the paste area when you move cells.
  • When you copy cells, cell references are automatically adjusted. When you move cells, however, cell references are not adjusted, and the contents of those cells and of any cells that point to them may be displayed as reference errors. In this case, you'll need to adjust the references manually.
  • If the selected copy area includes hidden cells, Excel also copies the hidden cells. You may need to temporarily unhide cells that you don't want to include when you copy information.

If the paste area contains hidden rows or columns, you might need to unhide the paste area to see all of the copied cells.

follow this link-

This step-by-step article describes the functionality of the Paste Options button in Microsoft Excel.
The Paste Options button becomes available when you paste data into cells in Excel. The button appears just below the pasted selection. When you click the Paste Options button, you are presented with a list of options that determine how the data will be pasted into the cell. These options are also available on the Edit menu when you click Paste Special.

Use Paste Options

The Paste Options button offers you the following options:
  • Keep source formatting: Preserves all original formatting of the pasted selection. Preserves all formulas as well.
  • Match destination formatting: Formats the pasted data to match the formatting already existing in the new location.
  • Values Only: This option appears only when the pasted data contains formulas. By choosing it, you strip all formulas and paste only the resulting values.
  • Values and Number Formatting: Formatting is preserved only for numeric values, including dates. In addition, only formula results (see "Values Only") are pasted. Text formatting is removed.
  • Values and Source Formatting: Strips all formulas (see "Values Only") and preserves all formatting.
  • Keep source column widths: Preserves the column widths as well as all formulas and formatting.
  • Formatting only: Pastes only the formatting, leaving all values and formulas out of the pasted range.
  • Link cells: Links the pasted cells to their original location. For example, if you copy A1 to B1, cell B1 will contain a link to the original location in the format "=A1". All formatting and formulas are removed.
  • Use Text Import Wizard: This option becomes available when you paste plain text from another program, such as Notepad. The Text Import Wizard allows you to specify how to divide pasted text into columns by using delimiter characters or defined text lengths to create individual columns.
Note that the exact set of options available depends on the data that you paste.

Use Multiple Actions

In some situations, it may be necessary to use a combination of the preceding options. For example, you may need to copy only the formatting and preserve the column widths. To do this, use the Paste Special function on the Edit menu.

Turn Off Paste Options

To prevent the Paste Options button from appearing, follow these steps, as appropriate for the version of Excel that you are running.

Microsoft Excel 2002 and Microsoft Office Excel 2003

  1. On the Tools menu, click Options.
  2. Click the Edit tab.
  3. Click to clear the Show Paste Options buttons check box.
  4. Click OK.

Microsoft Office Excel 2007

  1. Click the Microsoft Office Button, and then click Excel Options.
  2. On the Advanced tab, click to clear the Show Paste Options buttons check box.
  3. Click OK.

Jitendra Faye replied to Dan on 13-Sep-11 12:54 AM
Try this macro-

ActiveCell.Offset(1, 0).Select

Try this and let me know.
Radhika roy replied to Dan on 13-Sep-11 10:24 AM

Try this code-


Sub Copy_to_next_Available()


    Range("H65536").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)

End Sub


Hope this will help you.


Dan replied to Dan on 13-Sep-11 05:42 PM
I got it to work.  Thank you everyonre for the help.