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]

What are the values of i/j -- is j perhaps zero?

 

[FellowExcellor]

i is always populated - i.e it is the name of the sheet to go and fetch data from. This part of the process always work fine, it when the data is copied over that the error occurs.

j is also always populated with data, non zeros.

 

[FellowExcellor]

error when copying filtered data

Hello,

Was wondering if anyone had anymore thoughts about my problem? If it is an issue with copying filtered data using VB, then is their perhaps another way to copy the data I need without filtering (I.e any row that has WE in the column, max 1000 lastest rows)

Thanks

FE

 

[just_jon]

What exactly is it that you're wanting to do -- I can't get the rror to pop, but you are doing a bunch of unneeded Selects/Activates -- talk me thru what you want to have happen.

 

[FellowExcellor]

Jon, OK here's a bit more background on what I want to do.

Note the the code I am using is this, not the other one which I only set up as a test.

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

Basicly, I have several sheets with daily open, low, close, high for individual currencies. I have a sheet (lets call it summary) that pulls in daily data, then pupulates various charts and indicators (array formulas) based on the data it pulls in. So I select the currency what I want in the summary sheet (i in above code, it looks it up and pulls in the data and updates the charts and stuff. This all works fine.

Now I also want to analyse this data on a weekly basis - i.e look at weekly values. So in each currency sheet in col G I have a forumla that indicates the end of the week by W/E so that I can just copy and paste thoose values in.

I've created a seperate summary sheet for weekly data. Now I run exactly the same code but I ad in an extra bit of code:

Sheets(i).Columns("G:G").AutoFilter Field:=1, Criteria1:="WE"I sort

This filters in only thoose rows with WE in it. Then I copy all thoose rows, bottom upwards to a 1000:

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")

But unlike for the daily data where I use no filter, everytime I copy and paste the data I get the error message.

Hope this makes things a little clearer.

FE

 

[just_jon]

After this line

j = Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row 'finds last populated row

add

msgbox j

and report back the result.

 

[FellowExcellor]

Jon,

The message box returns 5646.

FE

 

[Krishnakumar]

Hi,

Sub ImportWeeklyData()
Dim sWS     As Worksheet, dWS   As Worksheet
Dim fRng    As Range
Set dWS = Sheets("Weekly Data")
Set sWS = Sheets("Evolution Group")
Set fRng = sWS.Range("G1", sWS.[g65536].End(xlUp))
dWS.[b2:g5000].Clear
With fRng
    .AutoFilter field:=1, Criteria1:="WE"
    .Offset(1, -6).Resize(.Rows.Count - 1, 6).Copy
    dWS.[b2].PasteSpecial xlValues
    .AutoFilter
End With
End Sub

HTH

 

[FellowExcellor]

Thanks for the code Kris.

But I see this is based on the second recorded macro which I only created to illustrate that copying and pasting FILTERED data from a sheet results in the error code. So this is really not much use to me!

Anyway, when I ran this code I still got an error. Can someone out there confirm that it is possible to copy and paste a selection from filtered data using VBA (as opposed to manually) into another sheet without generating the error?

Thanks

FE