Macro won't run in Excel 2013, works fine in 2010

[wdrvx]

Hello Guys,

I'm running a macro whose purpose is to copy certain ranges over to another worksheet. This used to work flawlessly in Excel 2007/2010, but for some reason it's giving me 'runtime error 1004' in 2013/2016. What's interesting is that part of the same code is executed just fine up until the last two bits of it.

I'm virtually clueless and would appreciate if someone could look and explain what's wrong with it.

Here's my code:

Sub copyRange()
Application.ScreenUpdating = False


Set srcSh = ActiveWorkbook.Worksheets("Source")
Set trgSh = ActiveWorkbook.Worksheets("Target")


Dim cont As Range
Dim start As Long
start = 2
For Each cont In srcSh.Range("BA3:BA500")
    If cont = "Yes" Then
        srcSh.Range(Cells(cont.Row, "J"), Cells(cont.Row, "J")).Copy
        trgSh.Range("A" & start).PasteSpecial Paste:=xlPasteAll
        srcSh.Range(Cells(cont.Row, "K"), Cells(cont.Row, "P")).Copy
        trgSh.Range("B" & start).PasteSpecial Paste:=xlPasteValues
        srcSh.Range(Cells(cont.Row, "T"), Cells(cont.Row, "T")).Copy
        trgSh.Range("H" & start).PasteSpecial Paste:=xlPasteValues
        srcSh.Range(Cells(cont.Row, "AK"), Cells(cont.Row, "AY")).Copy
        trgSh.Range("I" & start).PasteSpecial Paste:=xlPasteValues
        start = start + 1
    End If
    Next cont
End Sub

Problem occurs with the last two ranges (T:T & AK:AY), if I remove this bit of code, the rest runs just fine.
Thanks in advance for any advice!

 

[Tim_Excel_]

Since you are just copying values (and single cells for the T column), why not use

'...
trgSh.Range("H" & start).Value = srcSh.Cells(cont.Row, "T").Value
trgSh.Range(trgSh.Cells(start, "I"), trgSh.Cells(start, 23).Value = srcSh.Range(Cells(cont.Row, "AK"), Cells(cont.Row, "AY")).Value
start = start + 1
'...

Note that you also have to refer to the correct wb/sh for the Cells property

 

[steve the fish]

Its nothing to do with the excel version far as i can tell. The reason for the apparent randomness is that you have unqualified ranges:

srcSh.Range(Cells(cont.Row, "J"), Cells(cont.Row, "J")).Copy

This will error should srcSh not be the activesheet at the time the line executes. You need:

srcSh.Range(srcSh.Cells(cont.Row, "J"), srcSh.Cells(cont.Row, "J")).Copy

or as its just the one cell:

srcSh.Cells(cont.Row, "J").Copy

 

[wdrvx]

Its nothing to do with the excel version far as i can tell. The reason for the apparent randomness is that you have unqualified ranges:

srcSh.Range(Cells(cont.Row, "J"), Cells(cont.Row, "J")).Copy

This will error should srcSh not be the activesheet at the time the line executes. You need:

srcSh.Range(srcSh.Cells(cont.Row, "J"), srcSh.Cells(cont.Row, "J")).Copy

or as its just the one cell:

srcSh.Cells(cont.Row, "J").Copy

That does indeed work, thanks a lot!

Since you are just copying values (and single cells for the T column), why not use

'...
trgSh.Range("H" & start).Value = srcSh.Cells(cont.Row, "T").Value
trgSh.Range(trgSh.Cells(start, "I"), trgSh.Cells(start, 23).Value = srcSh.Range(Cells(cont.Row, "AK"), Cells(cont.Row, "AY")).Value
start = start + 1
'...

Note that you also have to refer to the correct wb/sh for the Cells property

This seems more reasonable than my current approach also, will give it a shot!

Thanks guys, helpful as always!

 

[Tim_Excel_]

Glad we could help!