PasteSpecial method of Range class failed

[Reddog94]

I've been reading like posts on mrexcel.com for an hour - but still cannot fix my problem.

I have what I think should be a pretty basic code to copy visible cells in one sheet, then paste values in the next sheet. I continue to get a run-time 1004 "PasteSpecial method of Range class failed". When the code runs in the document, I see the paste range (A1) highlighted, but then it bombs.

On Error Resume Next
 
With Range("EMPLOYEE_VISIBLE_RANGE").CurrentRegion
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy
End With
Err.Clear
 
ActiveWorkbook.Sheets("Sheet3").Range("A1").PasteSpecial xlPasteValues

Does anybody see what I'm doing wrong?

Thank you.

 

[Norie]

How many rows are in the range you are copying?

 

[Reddog94]

It's variable, based on a pull from a database query. If I had to copy paste a static number, 250 would probably do it.

 

[Norie]

What happens when you remove this?

On Error Resume Next

Do you get an error elsewhere in the code?

 

[Reddog94]

Same thing...

 

[Norie]

Is the destination sheet protected?

 

[Reddog94]

No, there isn't any protection in the workbook yet. In fact, I'm just using a blank sheet to test first. Once I figure out the code, then I will get it to paste in the proper tab.

 

[Norie]

It works fine for me.

What address appears in the message box with this code.

Dim rng As Range
 
    With Range("EMPLOYEE_VISIBLE_RANGE").CurrentRegion
        Set rng = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(12)
    End With
 
    MsgBox rng.Address
 
    ActiveWorkbook.Sheets("Sheet3").Range("A1").PasteSpecial xlPasteValue

PS Just out of curiosity why are you using SpecialCells? Has the data been filtered?

 

[Reddog94]

Subscript out of Range

 

[Norie]

Sorry I'm confused, that was the message?