Copy Problem From Advanced Filter

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,846
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am having a difficult time figuring out why mu code to copy the advanaced filter results from one worksheet to another is failing. The data to be filtered is in worksheet "Data" and the results of the filter ("Criteria A19:A20") are to be copied to "Dia_Temp A2"

The filter appears to be working OK, it's just not copying the data.

Here is my code:

Code:
    llastrow = 0
    ldiacopy = 0
    Sheets("Data").Activate
    With Worksheets("Data")
        If .FilterMode Then .ShowAllData
        llastrow = Range("a65536").End(xlUp).Row
        With .Range("A1:M" & llastrow)
            .AdvancedFilter _
                    Action:=xlFilterInPlace, _
                    CriteriaRange:=Worksheets("Criteria").Range("A19:A20"), _
                    Unique:=False
            On Error Resume Next
            Set rngToCopy = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
            ldiacopy = rngToCopy.Count
            If ldiacopy = 0 Then
                MsgBox ("There are no diamond records found." & chlr & "Press OK to continue.")
            End If
                 
        End With
         If .FilterMode Then .ShowAllData

     End With

     If Not rngToCopy Is Nothing Then rngToCopy.EntireRow.Copy Destination:=Dia_Temp.Range("A2")

Jenn
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
What does the variable Dia_Temp equal?
If it is a worksheet name you are using the wrong syntax.
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,846
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Ahhhh .... I have been enlightened.
I used the wrong variable there, I meant to use the variable I had defined earlier to specify the destination worksheet.

Jenn
 

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
To be safe, this line:
Rich (BB code):
llastrow = Range("a65536").End(xlUp).Row

Should be:
Rich (BB code):
llastrow = .Range("a65536").End(xlUp).Row




And here:
Rich (BB code):
            On Error Resume Next
            Set rngToCopy = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
            ldiacopy = rngToCopy.Count
            If ldiacopy = 0 Then
                MsgBox ("There are no diamond records found." & chlr & "Press OK to continue.")
            End If
                 
        End With
         If .FilterMode Then .ShowAllData
     End With
     If Not rngToCopy Is Nothing Then rngToCopy.EntireRow.Copy Destination:=Dia_Temp.Range("A2")
Your On Error Resume Next statement is covering up too many potential bugs (every line that follows). I think you could replace that code with:
Rich (BB code):
            'if there are no visible cells then we will get an error. we will ignore it.
            On Error Resume Next
            Set rngToCopy = .Offset(1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
            'resume normal error handling.
            On Error GoTo 0
 
        End With
         If .FilterMode Then .ShowAllData
     End With
 
     'did we find any diamond records?
     If rngToCopy Is Nothing Then
        MsgBox ("There are no diamond records found." & chlr & "Press OK to continue.")
     Else
        rngToCopy.EntireRow.Copy Destination:=Dia_Temp.Range("A2")
     End If
Not sure what chlr is though?
 

Watch MrExcel Video

Forum statistics

Threads
1,126,934
Messages
5,621,688
Members
415,851
Latest member
JohnZ1156

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top