run-time error 1004 when copying filtered data

[FellowExcellor]

Hi

Have the code below that takes a sheet refrence from the activesheet, goes to that sheet, filters data and then copies and pastes data from that sheet into the original sheet.

However everytime I run this macro, I get the error: run-time error 1004,
Copy method of range class failed.

Sub Importweeklydata()
Dim i As String, j As Long
Range("b2:g5000").Clear
i = Range("A1").Value
Sheets(i).Columns("G:G").AutoFilter Field:=1, Criteria1:="WE" 'filters on WE in the sheet
j = Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row 'finds last populated row
Sheets(i).Range("A" & Application.WorksheetFunction.Max(1, j - 1000) & ":f" & j).Copy Sheets("Weekly Data").Range("b2")
Sheets(i).Columns("G:G").AutoFilter 'unfilters the sheet
End Sub

When I debug it takes me to the line:

Sheets(i).Range("A" & Application.WorksheetFunction.Max(1, j - 1000) & ":f" & j).Copy Sheets("Weekly Data").Range("b2")


As a test, I tried manually copying and pasting the some procedure as above using the following recorded macro and get a similar error: "Paste method of worksheet class failed"

Sub testing()
Range("B2:F203").Select
Selection.ClearContents
Sheets("Evolution Group").Select
Columns("G:G").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="WE"
Range("B804").Select
Range("A2342").Select
ActiveWindow.SmallScroll Down:=4
Range("A1342:F2342").Select
Range("A2342").Activate
Selection.Copy
Sheets("Weekly Data").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Evolution Group").Select
Selection.AutoFilter Field:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=-14
Sheets("Weekly Data").Select
Range("A22").Select
End Sub

Again debugging takes me two the line: ActiveSheet.Paste

It seems the errors are occuring because I am copying filtered data. If I don't use the filter, the copy and paste seems to work fine in both cases.

Does anyone know why this is happening and what I can do about it?

Thanks

FE

 

[just_jon]

Oh, yes, it is quite possible.

Can you email the workbook?