Excel - Copy/Paste Filter Values

Hi,

In Excel 2007, the first row is filtered - Column A: # and Column B: information.

A ranges from 1 - 10. I filter on 4, and copy from Sheet 1, and past special values and format in Sheet 2. However, all values paste rather than just filetered 4.

This problem never happened in Excel 2003. Can you please advise?

Thank you.
tahirihAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RichardSchollarCommented:
Hi

Are you specifying just the visible cells?:

Richard
ActiveSheet.Autofilter.Range.SpecialCells(xlCellTypeVisible).Copy

Sheet3.Range("A1").PasteSpecial

Open in new window

jppintoCommented:
From the menu select Edit->Goto. Choose the "Special" button. Click the "Visible cells only" option. Press OK.

Now right click on any part of the resulting selection and choose "Copy". In a new sheet or workbook, right click again and choose "Paste".

jppinto

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tahirihAuthor Commented:
where is edit - goto in excel 2007?
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

RichardSchollarCommented:
Hi

Try hitting F5 function key - it achieves the same as Edit>Goto in previous Excel versions.  Otherwise (via the Ribbon) it's on the Home tab under Find & Select (far right hand side of Ribbon) and select Goto Special.

Richard
ksatrenCommented:
Hi,

Richard is correct here. I am not on the English version of Excel, but the menu is situated on the same spot;

1. Mark the area you want to copy
2. Follow Richard's instructions to find the menu above
3. Now only visible cells should be highlighted
4. Paste it in the other sheet

K
Rory ArchibaldCommented:
Do you have SP2 applied? I'm pretty sure that bug was fixed.
Note: you can also use Alt+; to select the visible cells only.
tahirihAuthor Commented:
Thank you everyone.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.