Use Autofilter to Copy Cells from Column A

[Bonman]

I've been looking for a macro to copy my results from an autofilter from column A and paste them into a specific worksheet. Basically I have a main sheet that contains kit numbers across the top and part numbers in column A. If the part number is in that kit it gets an X. I usually use the autofilter and filter the kit by using X in my criteria. Then I copy the list in column A and paste it in a sheet labeled with that kit number. With 54 kits it gets very time consuming so I’m looking for a way to speed it up by using a macro. The kits are numbered E1 thru E54 along the top in row 1 starting in column B. The part numbers are in column A and start in row2. Cell A1 is label Parts so when I copy the list the header is Parts. The main sheet is called Kits. Any help would be greatly appreciated!

 

[yytsunamiyy]

Bonman, welcome to the board.

2 suggestions:

If the Kit-numbers and sheets do not change and the part-numbers are unique, a formula-based approach using an INDEX(MATCH...)) approach would be easy to implement and be self-updating on re-calculation.

If you insist on a VBA-Solution investigate the use of Advanced Filter. See XL Help file for details on how to use. Then record a macro using advanced filter with copy to (Kit-NR. Sheet!A2). Post the recorded macro here and we can help you refine.

 

[jbeaucaire]

I actually helped someone create a sheet that does this very thing in almost exactly the same way. Items down the left and sheetnames across the top, X in the chart itself to signify which items should transfer over to the matching sheet.

I used a hidden "Key" column that creates a sequential number listing for each sheet to pull from, all in those hidden columns. Use the PLUS symbols across the top to hide/unhide them easily.

http://www.excelforum.com/attachmen...py-master-cross-reference-data-version-2-.zip

Since this happens in realtime, no macro needs to be triggered.

 

[Bonman]

Thank you both! I took yytsunamiyy's ideas and jbeaucaire spreadsheet for reference and was able to make it work perfect. The spreadsheet was almost identical to what I was doing and with a few changes to the formulas I was able to make it happen. Once again I thank the both of you!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>