Excel 2016 VBA to copy and paste not working

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
608
Office Version
  1. 365
Platform
  1. Windows
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)
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top