Copy data from one column to a new column in the same sheet.

[InnaG]

Hello,
I am working on a macro to find column names "Prov_Specialty" , add new column named "Specialty1" and copy data from the original column to the new column.

Here is how far did I get:

Dim Found As Range
Dim LR As Long
Set Found = Rows(1).Find(what:="Prov_Specialty", LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then Exit Sub
LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
Found.Offset(, 1).EntireColumn.Insert
Cells(1, Found.Column + 1).Value = "Specialty1"



Do not know how to copy data from Prov_specialty to Speciality1 columns. Please help.
Thank you

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub InnaG()
   Dim Found As Range
   Set Found = Rows(1).Find(what:="Prov_Specialty", lookIn:=xlValues, lookat:=xlWhole)
   If Found Is Nothing Then Exit Sub
   Found.EntireColumn.Copy
   Found.Offset(, 1).EntireColumn.Insert
   Found.Offset(, 1).Value = "Specialty1"
End Sub

 

[mumps]

Try:

Sub CopyCol()
    Application.ScreenUpdating = False
    Dim LastRow As Long, Found As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set Found = Rows(1).Find(what:="Prov_Specialty", LookIn:=xlValues, lookat:=xlWhole)
    If Not Found Is Nothing Then
        Found.Offset(, 1).EntireColumn.Insert
        Cells(1, Found.Column + 1).Value = "Specialty1"
        Range(Cells(2, Found.Column), Cells(LastRow, Found.Column)).Copy Cells(2, Found.Column + 1)
    End If
    Application.ScreenUpdating = True
End Sub

 

[InnaG]

Worked perfectly!!!
Thank you!!!

 

[Fluff]

Not sure which of us you are replying to, but glad we could help & thanks for the feedback