vba 创建一个搜索标题并复制粘贴列的 Excel 宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/399975/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create an Excel macro which searches a heading and copy-paste the column
提问by namin
I am new to Excel macros. I have some columns with headings scattered in many sheets. I would like to type a heading in some column which has the cursor and have the column with that heading copy-pasted to the column with the cursor.
我是 Excel 宏的新手。我有一些列的标题分散在许多工作表中。我想在某个带有光标的列中键入一个标题，并将带有该标题的列复制粘贴到带有光标的列中。
Is it possible to do this by recording a macro? How? If not, how do I do it programmatically?
是否可以通过录制宏来做到这一点？如何？如果没有，我该如何以编程方式进行？
回答by Fionnuala
Here are a few notes on code to copy a found column.
这里有一些关于复制找到的列的代码的注释。
Dim ws As Worksheet
Dim r As Range
Dim CopyTo As String
'You must run the code from a suitable active cell '
strFind = ActiveCell.Value
'Assumes the column data will be written into the next row down '
CopyTo = ActiveCell.Offset(1, 0).Address
'Look at each worksheet'
For Each ws In ActiveWorkbook.Worksheets
'Skip the active worksheet '
If ws.Name <> ActiveSheet.Name Then
'Get the last cell and row'
lc = ws.Cells.SpecialCells(xlCellTypeLastCell).Column
lr = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
'Use the first row for search range '
Set r = ws.Range(ws.Cells(1, 1), ws.Cells(1, lc))
'Find the first whole cell to match the active cell '
Set f = r.Find(strFind, , , xlWhole)
'If it is found ... '
If Not f Is Nothing Then
'Copy the whole column to the copy position '
ws.Range(ws.Cells(2, f.Column), _
ws.Cells(lr, f.Column)).Copy (ActiveSheet.Range(CopyTo))
End If
End If
Next
回答by namin
Does the heading appear many times across the different sheets? If not, then I would suggest using a simple if statement
标题是否在不同的工作表中多次出现？如果没有，那么我建议使用简单的 if 语句
if ('columnheading' = 'column to check','line1ref', 'line1refin other sheet')
if ('columnheading' = '要检查的列','line1ref', 'line1refin other sheet')
You will have to check each column to do this.
您必须检查每一列才能执行此操作。
Are there many sheets, are the columns always in the same position?
有很多张，列总是在同一个位置吗？
