?
Solved

Excel VBA Copying Data with Autofilter Crashes when there is no data to Copy

Posted on 2013-06-11
3
Medium Priority
?
501 Views
Last Modified: 2013-06-11
If there is data, the macro works perfectly but when there's no data in the AutoFilter, it crashes. What did I do wrong and how can I fix it? Thank you!!

' Copies Temperature errors to "Errors List"
With Sheets("All Data").Range("A3:T380")
    .AutoFilter
    .AutoFilter Field:=16, Criteria1:="1"
    .Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Sheets("Errors List").Range("F11")
    .AutoFilter
End With
    ActiveSheet.ShowAllData

' Copies Humidity Errors to "Errors List"
With Sheets("All Data").Range("A3:T380")
    .AutoFilter
    .AutoFilter Field:=14, Criteria1:="1"
    .Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Sheets("Errors List").Range("D11")
    .AutoFilter
End With
    ActiveSheet.ShowAllData
    
' Copies Operator Errors to "Errors List"
With Sheets("All Data").Range("A3:T380")
    .AutoFilter
    .AutoFilter Field:=11, Criteria1:="Yes"
    .Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Sheets("Errors List").Range("B11")
    .AutoFilter
End With
' Remove any filters
ActiveSheet.AutoFilterMode = False

Open in new window

0
Comment
Question by:crepe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 39238921
if there are no visible cells, your code will crash. You can use a On error resume next line, or you could test for lines before you run your copy, e.g. with a subtotal(3 (count), as in:

if application.worksheetfunction.subtotal(3,columns(1))>1 then
.Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy _
        Sheets("Errors List").Range("B11")
end if

Open in new window


Thomas
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39238990
Thomas, I'll have to remember that trick.  I typically just use On Error Resume Next :)
0
 

Author Closing Comment

by:crepe
ID: 39239022
Perfect! Thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question