Excel | Select Visible Cells Only

Select the area you want to copy. Press F5 click Special, click visible cells only click OK. Copy, then paste where you want.

http://www.mrexcel.com/forum/showthread.php?t=27706

There is a toolbar button for visible cells only if you use it lots:

In MS Excel 2007, to add the Select Visible Cells Command, do the following steps:
Step 1: Right click on the toolbar on top and click Customize Quick Access toolbar.
Step 2: Under Choose Command From drop down menu

, select Commands Not in the Ribbon.
Step 3: Scroll down and click on Select Visible Cells.
Step 4: Click on Add and OK.
Now you should be able to see the command in the quick access toolbar.

http://www.lytebyte.com/2008/02/08/how-to-select-and-copy-only-visible-cells-columns-or-rows-in-ms-excel-2007-and-2003/