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