VBA code to locate specific already filtered columns by name and paste into a new workbook

[JDru85]

Hi All,

So to give some background, i have a large data set (60k plus rows and 90 plus columns) that i need to filter and create a new workbook using specific columns.

The size of the file changes daily, and columns are taken out and added. So i had to keep updating the previous macro i had that relied on the columns staying the same position.

Now i am trying to recreate it using the specific columns names, so far I have successfully managed to write vba to apply and select filters to the 10 columns that required filtering. My issue is comes when i need to copy and paste the 26 columns required for the report into a new workbook, the code i have so far works perfectly except that the first column copied ignores the filters and pastes the entire column (60k rows), whereas all the remaining 25 columns only paste the filtered rows (85 rows).

Can anyone please provide some help/guidance on where i am going wrong or how to paste only the filtered rows for all 26 columns?

The code i have so far is below;

Sub Export_Report()

Dim ReqCol As Variant, sh1 As Worksheet, sh2 As Worksheet, i As Long, rng As Range

Set sh1 = ActiveWorkbook.Sheets("sheet name")

ReqCol = Array("col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8", "col9", "col10", "col11", "col12", "col13", "col14", "col15", "col16", "col17", "col18", "col19", "col20", "col21", "col22", "col23", "col24", "col25", "col26")

Workbooks.Add
Set sh2 = ActiveWorkbook.Sheets(1)
For i = LBound(ReqCol) To UBound(ReqCol)
Set rng = sh1.Rows(1).Find(ReqCol(i), , xlValues).EntireColumn
rng.Copy (sh2.Cells(1, i + 1))
Next

End Sub

Many thanks for your assistance.

 

[JLGWhiz]

Try modifyihng to:

Set rng = sh1.Rows(1).Find(ReqCol(i), , xlValues).EntireColumn.SpecialCells(xlCellTypeVisible)

 

[JDru85]

Try modifyihng to:

Set rng = sh1.Rows(1).Find(ReqCol(i), , xlValues).EntireColumn.SpecialCells(xlCellTypeVisible)

That has worked perfectly, many thanks for your help.

 

[JLGWhiz]

That has worked perfectly, many thanks for your help.

You're welcome,
regards, JLG