Jagdev Singh
Active Member
Hi Experts
I have the below code which moves list of columns from one sheet to another while considering the header name. Is it possible instead of mentioning column header name in an array the code ask me to select the column number say A, B, and C etc… and use them as an input and move their respective data to the new sheet.
Please let me know if I am unclear with your doubts.
Regards,
JD
I have the below code which moves list of columns from one sheet to another while considering the header name. Is it possible instead of mentioning column header name in an array the code ask me to select the column number say A, B, and C etc… and use them as an input and move their respective data to the new sheet.
Code:
Sub CopyColumnByTitle()
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets("Risk Score Result").Activate
Dim ws As Worksheet
Dim SearchCols(14) As String
SearchCols(0) = "Facility_name"
SearchCols(3) = "Country"
SearchCols(4) = "TSI"
SearchCols(5) = "Currency"
SearchCols(6) = "Cyclone"
SearchCols(7) = "Drought"
SearchCols(8) = "Earthquake"
SearchCols(9) = "Fire"
SearchCols(10) = "Flood"
SearchCols(11) = "Landslide"
SearchCols(12) = "Lightning"
SearchCols(13) = "Storm Surge"
SearchCols(14) = "Tsunami"
Dim i As Integer
'Find "Entity" in Row 1
With Sheets("Result").Rows(1)
For i = LBound(SearchCols) To UBound(SearchCols)
Set t = .Find(SearchCols(i), LookAt:=xlPart)
'If found, copy the column to Sheet 2, Column A
'If not found, present a message
If Not t Is Nothing Then
If Sheets("Temp").Range("A1").Value = "" Then
pasteCol = 1
Else
pasteCol = Sheets("Temp").Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1).Column
End If
.Columns(t.Column).EntireColumn.Copy _
Destination:=Sheets("Temp").Cells(1, pasteCol)
Else
MsgBox SearchCols(i) & " Not Found"
End If
Next
End With
End Sub
Regards,
JD