Copy filtered columns using Intersect

Question

Took below code from Stack Overflow. It was working good but now it shows:

Run-time Error: "Application defined or object defined error"

I have searched for other code but it at least uses 3 to 4 lines like finding first filtered row, last filtered row, selection and then copying.

Can anyone please let me know what went wrong in the below code

Sub Submission_SLA()

WeekS = ">=" & Application.InputBox(Prompt:="Enter Start Date", Default:=Format(Date, "dd mmm yyyy"), Type:=2)
WeekE = "<=" & Application.InputBox(Prompt:="Enter End Date", Default:=Format(Date, "dd mmm yyyy"), Type:=2)
Windows("Overall report.xlsb").Activate
 Sheets("Sheet1").Select
Range("A:N").AutoFilter Field:=10, Criteria1:=WeekS, Operator:=xlAnd, Criteria2:=WeekE
    Intersect(ActiveSheet.UsedRange.Columns("A:I"), ActiveSheet.UsedRange.Offset(1)).SpecialCells(xlCellTypeVisible).Copy
    Sheets("ORCA 7.5").Select
    Range("A2").Select
    ActiveSheet.Paste

End Sub

I guess this code explains my issue more clearly

Answer 1

The only reason that line would be giving you an error is if there are less than 2 cells that have values in columns A to I.

I can't tell you how to fix it since you provided neither sample data nor explanation of what you're trying to do.

Some advice here and here.

Also:

• MSDN : Range.SpecialCells Method (Excel)

• MSDN : Worksheet.UsedRange Property (Excel)

• MSDN : Application.Intersect Method

• MSDN : Developer’s Guide to the Excel 2010 Range Object (and Video)

Answer 2

if you want to keep it a one liner:

If Application.WorksheetFunction.Subtotal(103, ActiveSheet.UsedRange.Columns("A")) > 1 Then ActiveSheet.UsedRange.Columns("A:I").Offset(1).SpecialCells(xlCellTypeVisible).Copy