Filter Specific Column of Selected files to Master File

Hi,

I am using the below VBA Code to filter some specific column (Source file- Active Workbook) and paste in new work book

I need to do this activity for many files. can someone help to modify the code as below.

I need to select multiple files instead of active workbook, then one be one all files to be copied and pasted in new work book.

Example if i select 5 excel files, (All in same folder only) first excel file to be copied and pasted in new workbook (as per sample vba code) and then the new workbook to be saved as main file.
then the second excel file (out of 5) to be copied and pasted in last row of main file.

Currently i am opening one by one and running the macro. Macro is working fine, however instead opening one by one and running the code, i am seeking some one help to modify the code instead of active workbook to selection of excel files.

Kindly help to modify the above code.

Sub Dates()

Dim wb As Workbook: Set wb = ActiveWorkbook
Dim a: a = ActiveSheet.Range(Cells(6, 1), Cells(6, ActiveSheet.UsedRange.Columns.Count))
With ActiveSheet.Range("C1:C" & ActiveSheet.UsedRange.Rows.Count)
.Value = Evaluate(Replace("if(isnumber(@),@)", "@", .Address))
.Parent.UsedRange.AutoFilter 3, "<>False"
.Parent.UsedRange.SpecialCells(12).Copy
End With

Workbooks.Add
[A1].PasteSpecial xlPasteAll
[A1].Resize(, UBound(a, 2)) = a

Application.DisplayAlerts = False
wb.Close False
Application.DisplayAlerts = True

End Sub

Possibly...

Please Login or Register  to view this content.

The code is selecting excel files, but can you help me how to link your code with above my code.

Please Login or Register  to view this content.

Hi dangelor.

Thanks for your code. while running the code its opening the selected excel files. but copy and pasting in new sheet not happening.

"Workbooks.Add" kindly make this file as master file and then one by one pasted in this workbook.

In this bit of code:

Please Login or Register  to view this content.

you are creating a new workbook for every file, and you are pasting something into Range ("A1"), and then immediately overwriting it with the contents of the variable a.
What is it you are trying to accomplish? (Sample workbooks would be helpful to finding a solution)

Hi let me explain in detail for the the existing code
The existing code is not for multiple files from one Excel CSV workbook header starting from row 6, on row 6 column C filtering only dates rows (excluding other than date rows)then copying filtered data to new workbook starting from row A1

Currently I am trying to change the above code as below

The existing code I am doing for one csv file, in new code I need to select multiple csv files, each files to be copied (filtering columnC6 with only dates as per existing code) then to be pasted in one workbook. Means output should be single file, each file one by one copied and pasted in one workbook. We can give fixed name for this workbook as master file

Try this version...

Please Login or Register  to view this content.

Hi dangelor

Thank you so much. code is working as expected. only one issue is that, its copying other row if its having value. though column C is blank.

Hi Dangelor.

I have attached sample excel file for your reference. Refer Cell AH24 & A2 ,though column C is blank its coming in Output
C2 also coming in output though its not a "date row". Kindly help to fix this .

Place this code in a standard module in the Master workbook.

Please Login or Register  to view this content.

Hi Refer the output file (Master Latest) Row AH23
Though no data on Column C its its appearing in output file.
Kindly help to fix this.

And instead of "standard module in the Master workbook" is it possible to run the code from "Personal Macro workbook" (Earlier Code Worked)

Try this version...

Please Login or Register  to view this content.

Thanks for your code. Still other rows data is appearing in out put file, though no date in column "C".
Attached Sample file Refer AG 36 and AH36

out put File Refer AG23 and AH23 marked in yellow colour. Kindly help.

The latest csv file has rows that were not in the previous versions. Will your working csv files also have the added rows? If so, the autofilter section of the code will need to be revamped.

Yes I shared sample file however working file having added rows. It's having data in other columns excluding column C. Please help to revamp the code.

I'll try to get something before the end of today

One last try...

Please Login or Register  to view this content.

Hi dangelor Code is working fine after replacing "lRow = ws.Cells(1, 3).End(xlDown).Row " with lRow = ws.UsedRange.Rows.Count suggested by Nankw83. Thank you so much for your help.

Glad I was able to somewhat help.