PasteSpecial method on range fails

Question

I'm trying to paste a range of cells in another worksheet. This is what I have come up with so far:

For i = 1 To MyCount

    wbk.Activate

    Set Criteria = Sheets(IGMSheet).Cells(i, 1)


    Sheets(IGMSheet).Range(Cells(i, 2), Cells(i, 4)).Copy 'this copies the 3 cells I need

    w.Activate
    If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData 'remove autofilter

    Selection.AutoFilter

    Range("$A$1:$BM$204").AutoFilter Field:=2, Criteria1:=Criteria.Value

    Range("$BC$1:$BE$204").SpecialCells(xlCellTypeVisible).PasteSpecial


Next i

If I just update a value in the range then it works, but pasting the cells does not.

Kindly advice.

I don't know exactly what u mean friend. Do u want the format of cell? or value ? — Anonymous, Aug 08 '13 at 06:55
You are getting that error because you are not copying anything `JUST` before pasting :) Remember Excel has a nagging habit of clearing the clipboard... — Siddharth Rout, Aug 08 '13 at 06:56

score 2 · Accepted Answer · edited May 23 '17 at 10:29

2

Further to my comment above, try this...

Dim rng As Range

For i = 1 To MyCount
    wbk.Activate
    Set Criteria = Sheets(IGMSheet).Cells(i, 1)

    Set rng = Sheets(IGMSheet).Range(Cells(i, 2), Cells(i, 4))

    w.Activate
    If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData 'remove autofilter

    Selection.AutoFilter

    Range("$A$1:$BM$204").AutoFilter Field:=2, Criteria1:=Criteria.Value

    rng.Copy

    Range("$BC$1:$BE$204").SpecialCells(xlCellTypeVisible).PasteSpecial
Next i

There are few other things that I noticed. For example using .Activate and unqualified cells Range(Cells(i, 2), Cells(i, 4))

You might also want to see this

edited May 23 '17 at 10:29

Community

1
1

answered Aug 08 '13 at 06:58

Siddharth Rout

147,039
17
206
250

Siddharth, thanks for your reply. The above code works for me, but now when I add data in other sheets in IGMSheet (before they were empty) I am getting a runtime error "application-defined or object-defined error" on line Set rng = Sheets(IGMSheet).Range(Cells(i, 2), Cells(i, 4)) . I have also checked your link, but I cannot refer directly to the sheet name for the object w as it can be different each time. Please advice. – Kris Van den Bergh Aug 08 '13 at 14:34

PasteSpecial method on range fails

1 Answers1