Create an Excel macro that looks for a header and copies the column
I am new to Excel macros. I have multiple columns with headers scattered across many sheets. I would like to type a heading in some column that has a cursor, and have the column with that heading copied to the column with the cursor.
Can I do this by recording a macro? How? If not, how can I do it programmatically?
0
source to share
2 answers
Here are some code notes for copying the 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
+1
source to share
Does the title light up many times on different sheets? If not, then I would suggest using a simple if statement
if ('columnheading' = 'column to check', 'line1ref', 'line1refin other sheet')
You will need to check each column to do this.
Are there many sheets, are the columns always in the same position?
0
Oliver K
source
to share