Macro with Match or If then copy into a new Tab

Question

I have been stuck with a macro that I am building and I wonder if anyone can help me with it.

The Macro:

What it basically does, it pulls in into a the main workbook a number of excel files (tabs) with a huge range of raw data and then it consolidate the raw data pulled from all the Tabs imported into a new and standard dashboard tab used for monthly reporting. Each of the files imported come from a different desk located worldwide therefore each of them have a different format and the need to build a standard filed. Once all the files "tabs" are loaded/Pulled onto the main "Main" excel file, it extract specific information e.g.

This is one of the tabs imported from a number of workbooks to the "Main” workbook:

And this is the Macro that pulls in the data from the imported tabs and copies it to a new tab

'Exporting Raw Data from Imported Tab "Country A
'Derivative Class column “D8” = Security Type on Standard Dashboard column “I2” 
Sheets("Operations").Select
Range("D8").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Worksheets("Country A").Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).PasteSpecia 
Paste:=xlPasteValues

'Derivative Ticker column “E8” = Security Alias on Standard Dashboard column “F2”
Sheets("Operations").Select
Range("E8").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Worksheets("Country A ").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).PasteSpecial 
Paste:=xlPasteValues

'Fund column “F8”= Portfolio Group on Standard Dashboard column “A2”
Sheets("Operations").Select
Range("F8").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Worksheets("Country A").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial 
Paste:=xlPasteValues

ETC

It goes like this until all the data from all the columns are pulled into the Standard Dashboard

My question is the next:

The Ranges of columns on Country A can change due to external formatting and where once were Derivative Class it can be now Derivative Ticker and the data will be pulled therefore onto the wrong column in the Standard Dashboard.

I would be interested in a macro that will instead of copy the data from a manually fixed range e.g. D8, in this case {Range("D8").Select, Selection.copy …} to copy the data in the column by the title of the column e.g. Derivative Class, Derivative Ticker, Fund, Price .. Etc.

Something like:

If "Derivative Class" found 
then 
    copy "Range(Selection, Selection.End(xlDown)).Select" 
    to  "Worksheets("Country A").Cells(Rows.Count, "I").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues"

I hope you could show me some light here as I am pulling more than 30k of rows with data from several sources.

Answer 1

First off, you need to use the Find function to locate the column where the title appears. You can use this for both sheets to automatically find the column locations. A function like this would be useful which searches for a value given the sheetname and the row in which to look and returns the column number:

Function FindColumn(ShtName As String, SearchValue As String, SearchRow As Integer) As Integer

    Dim FoundRng As Range, Sht As Worksheet

    Set Sht = ThisWorkbook.Sheets(ShtName)

    Set FoundRng = Sht.Rows(SearchRow).Find(What:=SearchValue, _
                                            After:=Sht.Cells(SearchRow, 1), _
                                            LookIn:=xlValues, _
                                            LookAt:=xlWhole, _
                                            SearchOrder:=xlByColumns, _
                                            SearchDirection:=xlNext, _
                                            MatchCase:=True)

    FindColumn = FoundRng.Column

End Function

Then I would also condense your code into a subfunction since you are doing the same action every time (copy then paste between the same sheets everytime). This finds the column number where your specified header is located in both sheets and then copies the column from one sheet to the other. Something like this:

Sub CopyData(SearchValue As String)

    Dim InputColumn As Integer, OutputColumn As Integer

    InputColumn = FindColumn("Operations", SearchValue, 7)
    OutputColumn = FindColumn("Country A", SearchValue, 1)

    With ThisWorkbook.Sheets("Operations")
        .Range(.Cells(8, InputColumn), .Cells(8, InputColumn).End(xlDown)).Copy
    End With

    With ThisWorkbook.Sheets("Country A")
        .Cells(.Rows.Count, OutputColumn).End(xlUp).Offset(1, 0).PasteSpecial _
         Paste:=xlPasteValues
    End With

End Sub

Then with these two functions you could run your main routine like this:

Sub MainRoutine()

    CopyData "Derivative"
    CopyData "Derivative Ticker"
    CopyData "Maturity"
    ...

End Sub

Answer 2

You may find that the easiest solution would be to modify the raw data so it conforms to standard layout. Do this for every input, and make a macro recording. Then as you re-import the data from the same source, you can re-use the recorded macro.

If you want to use the same macro for every data import, you can write code to treat a specific source in a specific way. For example, all relevant data from Country A can be found in column C, D, and F. Whereas all data from Country B can be found in column A, C, and E.

if data_source = "Country A" then
    'copy data from col. C8, D8 and F8
elseif data_source = "Country B" then
    'copy data from col. A8, C8, and E8
end if

The other alternative is to set up macro that will automatically recognize heading keywords and interpret them. This takes a lot more work. Unless you expect the files to change constantly, it might be easier to tweak your import procedure as you go.