带有“匹配"或“如果"的宏，然后复制到新的标签页中 [英] Macro with Match or If then copy into a new Tab

问题描述

我一直在使用自己正在构建的宏，我想知道是否有人可以帮助我.

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

宏:

它的基本功能是，将带有大量原始数据的许多excel文件(选项卡)引入主工作簿，然后将从导入到新的标准仪表板中的所有选项卡中提取的原始数据合并标签用于每月报告.导入的每个文件都来自世界各地的不同部门，因此每个文件都有不同的格式，并且需要构建标准文件.一旦所有文件标签"都加载/拉到主主要" excel文件中，它就会提取特定信息，例如

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

我的问题是下一个:

国家(地区)上的列范围可能会由于外部格式而发生变化，并且以前是 Derivative Class 的地方，现在可以是 Derivative Ticker 和因此数据将被拉到标准信息中心"中的错误列上.

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.

我会对宏感兴趣，而不是从手动固定范围(例如 D8 ，在这种情况下为 {Range("D8").Select，Selection.copy…} ，以按列标题复制列中的数据，例如衍生品类，衍生品股票代码，基金，价格 ..等等.

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.

类似的东西:

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"

我希望您能在这里给我一些启发，因为我正在使用来自多个来源的数据提取超过30k的行.

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

推荐答案

首先，您需要使用查找"功能找到标题出现的列.您可以将其用于两个工作表以自动查找列的位置.这样的函数将很有用，它会在给定工作表名称和要查找的行的情况下搜索值，并返回列号:

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

这篇关于带有“匹配"或“如果"的宏，然后复制到新的标签页中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！