run-time error 1004 when copying filtered data

FellowExcellor

Board Regular
Joined
May 17, 2005
Messages
59
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

FellowExcellor

Board Regular
Joined
May 17, 2005
Messages
59
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

Board Regular
Joined
May 17, 2005
Messages
59
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

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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

Board Regular
Joined
May 17, 2005
Messages
59
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

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

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.
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
Hi,

Code:
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

Board Regular
Joined
May 17, 2005
Messages
59
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
 

Forum statistics

Threads
1,136,655
Messages
5,677,017
Members
419,668
Latest member
DharmaK

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