Copy Paste data in another sheet there's got to be a better solution using Autofilter?

[marcelita03]

Hello thanks for stopping to read my post.
Issue:
1- I have a cell ("L1") function that reads the user's laptop ID number in worksheet1
2- I have a macro that looks at that cell ("L1"), and looks that value in 2 places in worksheet2:
a- If the L1 value is found in column D, the entire row is copied to worksheet3
b- If the L1 value is not found in column D, the macro looks in column F
c- If the L1 value is found in column F, the entire row is copied to worksheet3
3. The macro refreshes a Pivot Table out of worksheet1

So in plain words, what my macro does is to recognize who you are (employee ID, column D), and display only the data that belongs to you in a Pivot Table.
Your manager also gets to see it (manager ID, column F) My problem is... the data is huge (60K+ rows) and this method works BUT it takes forever!

I have tried for days to improve my macro... maybe using Autofilter? I just can't figure it out. Any thoughts will be greatly appreciated it
This is my macro today

Sub SlowestMacroEver()
  
Dim wkbCurrent As Workbook
Dim wksCopySet As Worksheet
Dim wksDataSet As Worksheet
Dim wksUserSet As WorksheetDim strNameMgr As String
Dim strNameEmp As String
Dim strUserName As String
Dim intDataRow As Long
Dim intCopySet As Long

Application.ScreenUpdating = False

Set wkbCurrent = ActiveWorkbook
Set wksDataSet = wkbCurrent.Sheets("worksheet2")
Set wksUserSet = wkbCurrent.Sheets("worksheet1")
Set wksCopySet = wkbCurrent.Sheets("worksheet3")

Unhide '' this is a submacro that unhide all worksheets
DeleteRows ' this is a submacro that deletes all rows in worksheet 3 to clear everything before the macro runs


strUserName = wksUserSet.Cells(1, 12)  ' This is "L1"  

intDataRow = 2
intCopySet = 2
strNameMgr = wksDataSet.Cells(intDataRow, 4)  ' This is column D
strNameEmp = wksDataSet.Cells(intDataRow, 6)  ' This is column F
    Sheets("worksheet2").Select
    
Do Until strNameMgr = ""
    strNameMgr = wksDataSet.Cells(intDataRow, 4)
    strNameEmp = wksDataSet.Cells(intDataRow, 6) ' Here the macro starts looking for the "L1" value in D and F 


    If strNameEmp = strUserName Or strNameMgr = strUserName Then
        wksDataSet.Select
        Rows(intDataRow).Select
        Selection.Copy
        wksCopySet.Select
        Rows(intCopySet).Select
        Selection.PasteSpecial Paste:=xlPasteValues      'Here the data gets copy/pasted values into worksheet3
        intCopySet = intCopySet + 1
       End If
        
    intDataRow = intDataRow + 1
    
    Loop
       
     
    Exit Sub
       
Application.ScreenUpdating = True
         
End Sub

 

[TimNevins]

You could try using Advanced Filter. I use it in scenarios like yours. Put your laptop ID in a criteria block (field name, then value beneath.

You can have your macro kick off when the laptop ID is updated to do an advanced filter based on your criteria. There is lots of information on using advanced filter online. It will filter all data that matches your criteria.

If you make "criteria", data table, and "copy to" ranges defined names it makes it easier to manage and is more flexible.

Once you get the hang of it, advanced filter is pretty simple. The macro to make it happen is pretty simple too.

Good luck!

 

[marcelita03]

Have you seen that Advanced Filter macro? I have tried several with no luck. It gets confusing because the criteria is in 2 different columns (D or F)
There is a lot of info on Advanced Filter.. but I can't get a macro that works for my case

 

[TimNevins]

Once you get the hang of the advanced filter it’s quite easy to use. It complains a lot until you get everything configured correctly.

The macro I use is three lines long
Range("L4").Select – selects the top left corner of my copytorange
Range("DataSource").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("M1Crit"), CopyToRange:=Range("M1Ext"), Unique:=True
Datasource = a defined name that includes all of my data including the column headings
M1Crit = defined name which includes all criteria values
M1Ext = defined name that selects the column headings (only) in the copytorange.

Your criteria and your copy to area DO NOT need to include all of the column headings in the datasource.

To get an OR criteria for Advanced filter do this:
Column Headings D F
Criteria row 1 L1
Criteria row 2 L1

Just to be clear, L1 is on the first row below heading D, L1 is on the second row beneath heading F.

If you put them both on Criteria row 1 it function as an AND and only rows where L1 is in both D and F will be returned.