Excel–Pasting into visible cells only

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/pasting-into-visible-cells-only/ebec7959-60bd-4903-ad8e-391f2f8d8479?page=2

Here are some detailed instructions and a faster version of the above code that will not mess with your formatting and won’t paste formulas (ie: paste values ONLY):

1) Save your file as an XLSM file, so that you can run “macros” or VBA code.

2) In the Developer Ribbon ( www.google.ca/search?q=show+developer+ribbon ) click “View Code”

3) The VBA window opens up.  On the right, open up the Modules folder and double click on Module1 to open it.

4) Copy and Paste this code below into the main part of the window where the code goes, to the right:

Sub Copy_Filtered_Cells()
Set from = Selection
Set too = Application.InputBox(“Select range to copy selected cells to”, Type:=8)
Application.ScreenUpdating = False
For Each Cell In from
Cell.Copy
For Each thing In too
If thing.EntireRow.RowHeight > 0 Then
thing.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set too = thing.Offset(1).Resize(too.Rows.Count)
Exit For
End If
Next
Next
End Sub

5)  If you want to copy more than JUST the values (such as formats or formulas), then remove the following part ONLY:

Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Be sure to LEAVE IN the “thing.PasteSpecial” part!

6) Save

7) Highlight / SELECT the single column of data that you want to copy, but don’t actually COPY it, just SELECT it.

8) Press ALT and F8 for bring up the Macros window.  Find “Copy_Filtered_Cells()”, click it, then click “Run”

9) Similar to step 7, go SELECT the cells where you want to paste into.  This is likely a column in a filtered list where some rows are hidden, and you only want to paste into the visible cells in that column. Be sure to SELECT the same number of cells to paste into as you selected to copy from.  If you only select say the first cell and run this, it’ll stop after only a few items.

10) Click OK or press ENTER.

11)  Wait.  The hourglass / circle on your mouse pointer will show you that Excel is working.

12) The macro takes you back to your copy selection when it’s done!

13) Close the Microsoft Visual Basic Window, Save your file.  If you like, you can remove the macro by saving it as XLSX now, or keep it as XLSM to use in future.

19 people found this helpful
Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s