Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Excel 2016 VBA to copy and paste not working
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2004
    Posts
    435
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel 2016 VBA to copy and paste not working

    Hello,
    Iíve been using the code below for many years and when I updated to Excel 2016, the results are no longer pulling correctly. After the filter is set, the range thatís pasted to the new workbook is showing cells that are hidden and if some of the cells do not contain data, its shifting some of the rows.

    Im wondering if I need to make the rng.offset line into two separate lines, one to copy and one to paste. I tried a few versions but didnít have the syntax correct.

    Any help is appreciated.

    Code:
     'Copy filtered range to new wkbk
      With Workbooks(mydatafile).Worksheets("data")
            Set Rng4 = .AutoFilter.Range
            Set Rng4 = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count).SpecialCells(xlCellTypeVisible)
            Set Rng = .AutoFilter.Range
    
    
    'Copy filtered range to new sheet
     Rng.Offset(1, 0).Resize(, 24).Copy Destination:=Workbooks(TEMPFILE).Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp)(2, 1)

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,198
    Post Thanks / Like
    Mentioned
    56 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Excel 2016 VBA to copy and paste not working

    I am unable to recreate your problem in my version of Excel, but here is something you may not have tried

    instead of these 2 lines ..
    Code:
    Set Rng = .AutoFilter.Range
    
    Rng.Offset(1, 0).Resize(, 24).Copy Destination:=Workbooks(TEMPFILE).Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp)(2, 1)
    try this combination ...
    Code:
    Set rng = .AutoFilter.Range.Offset(1, 0).Resize(, 24).SpecialCells(xlCellTypeVisible)
    
    rng.Copy Destination:=Workbooks(TEMPFILE).Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp)(2, 1)

  3. #3
    Board Regular
    Join Date
    Nov 2004
    Posts
    435
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2016 VBA to copy and paste not working

    Thank you for responding. I like that you were able to combine my lines of code into two lines. However, I get the same results.

    If I manually apply the filter and select the "Go to Special" and select "Visible Cells Only", when I manually paste the range its correct. I recorded those steps to see if there was anything different but other than the Select commands, I don't see why my code won't work.

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,198
    Post Thanks / Like
    Mentioned
    56 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Excel 2016 VBA to copy and paste not working

    I am puzzled too

    What happens when you try this?

    Code:
    Set rng = .AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)
    
    For Each cel In rng
       cel.Resize(, 24).Copy Destination:=Workbooks(TEMPFILE).Worksheets("sheet1").Cells(Rows.Count, 1).End(xlUp)(2, 1)
    Next cel

  5. #5
    Board Regular
    Join Date
    Nov 2004
    Posts
    435
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2016 VBA to copy and paste not working

    I let the code run and after 4 minutes, I had to stop it from the task manager. It seems to get stuck on the cel.resize command line. I don't the code moving past that line.

    Its so bizarre that if I record my keystrokes, it works

    Selection.Copy
    Selection.SpecialCells(xlCellTypeVisible).Select
    Windows("Book2").Activate

  6. #6
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,198
    Post Thanks / Like
    Mentioned
    56 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Excel 2016 VBA to copy and paste not working

    As you say.. it's bizarre

    Your workbook itself may be the problem. So let's eliminate that possibility

    Close Excel completely
    Create a NEW workbook
    Add some sample data and filter manually before testing the copy visible cells & paste code there

  7. #7
    Board Regular
    Join Date
    Nov 2004
    Posts
    435
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2016 VBA to copy and paste not working

    I dont think its the workbook since I ran the macro from an older version of Excel on another computer and it worked perfectly.

    I'm not thrilled with this code but it seems to be working. Haven't done a complete review but it gets me past the problem area. Have to jump on a flight and will check it out later.

    Thanks for your help.



    Code:
      With Workbooks(mydatafile).Worksheets("data")
      
    'Copy filtered range to new sheet
      Set rng = .AutoFilter.Range.Offset(1, 0).Resize(, 24).SpecialCells(xlCellTypeVisible)
     
     End With
        
        rng.Copy
        
        With Workbooks(TEMPFILE).Worksheets("sheet1")
            .Activate
            .Range("A1").Activate
            .Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With

  8. #8
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,198
    Post Thanks / Like
    Mentioned
    56 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Excel 2016 VBA to copy and paste not working

    I dont think its the workbook since I ran the macro from an older version of Excel on another computer and it worked perfectly
    - I'd expect that to work but it proves nothing you did not know already (ie the code used to work in an old workbook running an old version of Excel)

    - that is not the same test as running the code in a workbook that has been newly-created in Excel 2016

    The code DOES work on a "clean" workbook created in Excel 2016 on my PC but you need to confirm that for yourself on your PC
    - my expectation is that the code will perform as required
    - I think it likely to be the result of something that has changed between Excel versions and the quickest fix is likely to be to dump your old workbook having carefully copied the data and formulas across

    Do you remember on which version of Excel your worbook was originally created?
    Does the filtered range happen to be a structured Excel Table?

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,198
    Post Thanks / Like
    Mentioned
    56 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Excel 2016 VBA to copy and paste not working

    I'm not thrilled with this code but it seems to be working
    here is a more concise alternative

    replace
    Code:
        With Workbooks(TEMPFILE).Worksheets("sheet1")
            .Activate
            .Range("A1").Activate
            .Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
    with
    Code:
        Application.Goto Workbooks(tempfile).Worksheets("sheet1").Range("A2")
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

  10. #10
    Board Regular
    Join Date
    Nov 2004
    Posts
    435
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel 2016 VBA to copy and paste not working

    Ok, I give it a try with opening a fresh new workbook in 2016. I was originally looking for a quick fix but Im sure i would have to rebuild it at some point. Ill let you know if it works.

    Thank you

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
  •