Autofilter results into another worksheet.

[JayLee]

Basically, I'm trying to autofilter something like the word exchanges from a number of worksheet and have it consolidate into one sheet. I basically autofilter, offset one row, Selection.End(xlDown).Select, and to the right. How do I tell excel to go to the last blank row in the consolidated and paste the information there? Please help! Thanks in advance!

 

[zzydhf]

on the consolidated sheet:
Range("A65536").Select ' assuming column a always has data
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select


also, be sure to only select the visible cells of the autofilter or you may be taking more data that you want
e.g.
Selection.SpecialCells(xlCellTypeVisible).Select


_________________
Oh, you hate your job? Why didn't you say so? There's a support group
for that. It's called EVERYBODY, and they meet at the bar. - Drew Carey
This message was edited by zzydhf on 2002-10-04 21:51

 

[JayLee]

The code works perfectly. Somehow it crashes though. What happens is that, if the autofilter picks up the first filtered but no results worksheet, it just copies the top row into the worksheet. After it does that, the 2nd spreadsheet has data and that data goes into the consolidated spreadsheet with no problem. However with the 3rd spreadsheet, there aren't any rows after it's filtered but then it crashes and gives me the Run-time error 1004. Why is it doing this? It worked for the other sheets. Thanks for all your help!

 

[zzydhf]

It may give you the error because there is nothing to copy. You may hve to check to see if there is data before running the copy.
I'm not sure what the code would be, but you could have cell at the top or bottom that does a subtotal and check it first...

 

[theguy]

Consolidated Sheet

Sorry about bringing back a very old topic but I believe that it relates to a question that I have. My spreadsheet is basically a list of flowers that will be used in making floral arangements for parties. Column A is the list of every possible flower to be used and the succeding columns are party a, b, c, etc. Under each party column is the amount of the various types of flowers that will be used and more importantly is that not every flower will be used a once ofcourse. The final column contains the totals for each flower being used.

My goal for another sheet is to contain a consolidated list of flowers that are only being used, respectively. Meaning, the flowers that are not being used will not show up in this sheet.

Am I going to have to use macro or is their some other way with autofilter or something? Thank You

 

[theguy]

bump