none
Problem with copying and pasting with VBA in Excel 2013 RRS feed

  • Question

  • I'm attempting to process a large number of large files and need to copy/paste formulas from one range of cells to another. The code I'm using is this:

        Range("I12:P12").Select
        Selection.Copy
        For i = 13 To 3000
            ActiveCell.Offset(1, 0).Select
            ActiveSheet.Paste
        Next

    When I run the code, instead of pasting the copied formulas into the destination cells, the program pastes the formulas into the corresponding cells of ANOTHER open workbook. I wrote the code in Excel 2010 and it worked fine. The original code was much simpler. Rather than the For...Next structure, I was able to select the destination cells using a blanket range statement:

        Range("A1:A3000").Select
        ActiveSheet.Paste

    When I ran it on Excel 2013 it began behaving badly.

    I need the other workbook open as it provides inputs to the code that is being executed. There is, however, no code for writing to the other open workbook. The only copy location is the active worksheet in the active workbook.

    Any thoughts?

    Friday, August 12, 2016 7:20 PM

All replies

  • Found a solution in another thread that fixed the problem. Using Selection.PasteSpecial xlPasteAll. Still, the original question remains. Why doesn't ActiveSheet.Paste work?

    Friday, August 12, 2016 9:42 PM
  • Found a solution in another thread that fixed the problem. Using Selection.PasteSpecial xlPasteAll. Still, the original question remains. Why doesn't ActiveSheet.Paste work?


     Glad you found a solution.  Just so you know for the next time though,  this is a VB.Net forum,  not a VBA forum.  8)

    If you say it can`t be done then i`ll try it

    • Edited by IronRazerz Friday, August 12, 2016 10:13 PM
    Friday, August 12, 2016 10:13 PM
  • Couldn't find a VBA forum. Please enlighten me.
    Friday, August 12, 2016 10:14 PM
  • Here is a search result for "VBA Forum" which lists some of them.  8)

    VBA Forum Search Results


    If you say it can`t be done then i`ll try it

    Friday, August 12, 2016 10:29 PM
  • Couldn't find a VBA forum. Please enlighten me.

    You want the Excel for Developers forum.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Proposed as answer by IronRazerz Saturday, August 13, 2016 4:22 PM
    Saturday, August 13, 2016 1:56 PM
  • Hi,

    >>When I run the code, instead of pasting the copied formulas into the destination cells, the program pastes the formulas into the corresponding cells of ANOTHER open workbook.

     

    Are you copying one range in a workbook and then pasting into another workbook?

    When using Range.Select and  Selection.Copy in the source workbook, Excel focuses on this source workbook, so when using Activesheet.Paste, it pastes all contents into this source workbook.

    To avoid confusing which worksheet is active, I suggest you use Workbook.Sheets.Range to get reference.

    Or use Workbook.Activate Method (Excel) to activate target workbook before pasting.

    Monday, August 15, 2016 9:38 AM
    Moderator
  • >>When using Range.Select and  Selection.Copy in the source workbook, Excel focuses on this source workbook, so when using Activesheet.Paste, it pastes all contents into this source workbook.<< 

    My experience suggests otherwise - the cells are being copied into a DIFFERENT worksheet in a DIFFERENT open (but not active) workbook. Interestingly, the cells are only copied to the FIRST row in the other workbook. All the while, there is nothing in coding to activate the other workbook. This is the exact code that I'm using. Nowhere am I activating another workbook. 

    Range("I12:P12").Select
        Selection.Copy
        Range("A1:A3000").Select
        ActiveSheet.Paste

    The source is the active worksheet in the active workbook, as is the destination. I wouldn't think I would need to activate the active workbook.

    I've run into this problem several times now. The only way I have found around it is to use

        Range("A1:A3000").Select

    Selection.PasteSpecial xlPasteAll


    Gary Christopher



    Monday, August 15, 2016 10:09 AM
  • Hi,

    Since I couldn’t reproduce your issue, could you please share the detail steps you are using? Where do you create the sub and if any code between Copy and Paste activate or refer to the inactive workbook?

    For a general solution, I suggest you refer to the specific sheet you target to when pasting. 


    Wednesday, August 17, 2016 5:43 AM
    Moderator
  • I feel that I've provided sufficient detail in my original post. The code is very specific (to my mind). 

        Range("I12:P12").Select
        Selection.Copy
        Range("A1:A3000").Select
        ActiveSheet.Paste

    I found a workaround, but that doesn't answer the question as to why the following works but the preceding doesn't:

        Range("I12:P12").Select
        Selection.Copy
    Range("A1:A3000").SelectSelection.PasteSpecial xlPasteAll


    Gary Christopher

    Wednesday, August 31, 2016 11:56 AM
  • Hi,

    What formulas do you use in the cells?

    Do you try to input fixed value in the cells and then copy/paste? If it would lead to different result?

    Do you try to create a new Sub and test the copy/paste code snippet?

    Since the cell formulas you are using are based on the other opened workbook, I think you could comment out your project sections by sections to check what cause the issue.

    Friday, September 2, 2016 1:21 PM
    Moderator