File Open and Search column from A1:CE1. If matched than copy entire row for that column.

Search column from A1:CE1. If matched than copy entire row for that column.

I have a folder, which contains 1000 of excel workbook, I am trying to open workbook 1 by 1. In sheet 1 I am searching all columns using case.

Now problem is that I want to copy entire row 1 by 1 if column matched.
For example:

Case "Date"
                            WSDest.Range("B" & LastRow) = _
                            col.End(xlDown).Value
                            'col.Value
                            'Range(Selection, Selection.End(xlUp)).Select

Your help will be much appreciated.

Hi Shivan

Please use Code Tags, first edit your post by adding code tag to your code

after editing your thread provide sample workbook of master file & source file

Hi, shivanisachdeva167,

your code:

'...
                    For Each col In Worksheets("Sheet1").Range("A1:CE1")
                                       
                       Set col = Worksheets("Sheet1").Range("A1:CE1").Value
                        Select Case col

try

                    For Each col In Worksheets("Sheet1").Range("A1:CE1")
                                       
'                       Set col = Worksheets("Sheet1").Range("A1:CE1").Value
                        Select Case col

If the column is equal in both sheets you could refer to that column in both commands but you would need to make sure to copy more than just the heading..

Ciao,
Holger

Hi, shivanisachdeva167,

It is not working for me.

Did you receive a run time error, which one? What didn´t work? You would need to elaborate on that.

I am trying to give some intelligence power to my macro.

I´d go with an Application.Match from the destination workbook on the new worksheet rather on that as I´m afraid VBA won´t be "intelligent" enough to asssume what you are trying to get, you would need to code it in order to get the proper result. You could use arrays for that although I don´t see the benefit. And you would make sure to havbe the same items being used the headings in every workbook/worksheet as well. If these differ you could try Find or loop and InStr or Like or use an array with synonyms for each item.

Ciao,
Holger

Hi, shivanisachdeva167,

the command you posted is sort of overload. Cells refers to all cells on a sheet, so what should CurrentRegion do about that? For Cells the entire sheet is the Cuzrrent Region. If you use a single cell or a small range you may find and use the surrounding region.

Application,Match will deliver the index of the range being tested if the item is found and in that case delivers a long value while if no match is found an error is delivered.

You would need to either use Resize for both areas or use copy/paste for just one cell.

Ciao,
Holger