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.
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)