Excel 2016 VBA to copy and paste not working

rex759

Active Member
Joined
Nov 8, 2004
Messages
483
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)
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,637
Office Version
365
Platform
Windows
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)
 

rex759

Active Member
Joined
Nov 8, 2004
Messages
483
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.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,637
Office Version
365
Platform
Windows
I am puzzled too :confused:

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
 

rex759

Active Member
Joined
Nov 8, 2004
Messages
483
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,637
Office Version
365
Platform
Windows
As you say.. it's bizarre :confused:

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
 

rex759

Active Member
Joined
Nov 8, 2004
Messages
483
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
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,637
Office Version
365
Platform
Windows
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?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,637
Office Version
365
Platform
Windows
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
 

rex759

Active Member
Joined
Nov 8, 2004
Messages
483
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,615
Messages
5,445,494
Members
405,335
Latest member
Thomanji

This Week's Hot Topics

Top