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)
 
Update, my original code works fine. The issue my computer. Thanks for your help
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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