Results 1 to 5 of 5

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

  1. #1
    New Member
    Join Date
    Oct 2016
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

  2. #2
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

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

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

    Code:
    '...
    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
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

  3. #3
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,648
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

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

    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:

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

    Code:
    srcSh.Range(srcSh.Cells(cont.Row, "J"), srcSh.Cells(cont.Row, "J")).Copy
    or as its just the one cell:

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

  4. #4
    New Member
    Join Date
    Oct 2016
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by steve the fish View Post
    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:

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

    Code:
    srcSh.Range(srcSh.Cells(cont.Row, "J"), srcSh.Cells(cont.Row, "J")).Copy
    or as its just the one cell:

    Code:
    srcSh.Cells(cont.Row, "J").Copy
    That does indeed work, thanks a lot!

    Quote Originally Posted by Tim_Excel_ View Post
    Since you are just copying values (and single cells for the T column), why not use

    Code:
    '...
    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!
    Last edited by wdrvx; Mar 19th, 2019 at 08:26 AM.

  5. #5
    Board Regular Tim_Excel_'s Avatar
    Join Date
    Jul 2016
    Posts
    512
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    4 Thread(s)

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

    Glad we could help!
    Always make a backup of your data before trying out any code

    Before you ask why my code is not working: have you checked if the references are correct? Are you referring to the correct Workbook/-sheet?

    Use [CODE] tags when pasting code in your posts

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •