Filtering and copying a sheet to another sheet... not working as planned!

[Jed Shields]

Morning all,

I'm trying to filter a sheet and then copy the filtered data to another sheet. What I've got below works but it balloons the size of the workbook up to 180Mb! I'm assuming it's because I'm copying the entire columns rather than just the filtered data...?

Any thoughts on a better approach?

Sub CopyTIData()

Dim LastRow As Long

 LastRow = Range("AJ" & Rows.Count).End(xlUp).Row

    ActiveSheet.Range("A1:BI" & LastRow).AutoFilter Field:=37, Criteria1:= _
        "T&I IT"


    Range("A:BI").Select
    
    Selection.Copy
    
    Sheets("T&I Data").Select
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
 Sheets("Sheet1").Select
 
 ActiveSheet.ShowAllData
        
End Sub

Cheers,

Jed

 

[vcoolio]

Ah. I see Jed. I'm glad that you're on top of it.

Cheerio,
vcoolio.

 

[Jed Shields]

I wouldnt say I'm on top of it, but definitely stumbling in the right direction :D

I've got another filter and copy query if you're up for it? This time I need to filter from the same sheet (All Data Current) column 61 for unique names only and paste the unique names to sheet 'Starter's' cell A2 and below...

 

[vcoolio]

Do you mean that you have a list of names and you wish to select an individual name and filter on it or just filter on the whole list of names?

Cheerio,
vcoolio.

 

[Jed Shields]

It's a list of names that can occur more than once, so I need to do an advanced filter for unique values only. I was hoping to do it all in code.

 

[vcoolio]

Hello Jed,

You could use the same code as in post #2 .
Change the filter column to 61 and for the criteria, do the following:-

- In a separate sheet, list the names that need to be included in the filter starting in row2, Column A. Give the list a name in row1 (say, CriteriaList).
- Give the list a defined name: high-light the entire list including the name then go to the formulas tab, select Define Name from the Defined Names group. In the dialogue box that appears, check all details then click OK.
- Next, convert the list to a table. High-light the list again, go to the insert tab, select table. A "Create Table" dialogue box will appear. Tick the "My table has headers" box then click OK.
You now have a table of names that will expand and contract as you add/delete names. On your main sheet, select a cell and create a data validation list using the table as the data validation source.

Let's assume that you have selected cell BJ1 in which to create the data validation list. So, in the code, replace the criteria with:-

ActiveSheet.Range("BJ1").Value

Hence, if, for example, you select "Bob" from the data validation drop down list, then all entries with "Bob" in column 61 will be filtered, copied and pasted to the new sheet. You'll need to also change the destination sheet name in the code.

I hope that this helps.

Cheerio,
vcoolio.

 

[Jed Shields]

Thanks, I'll take a look when I get chance