Find a column header, then copy and paste the column to a different location

[carefreeant88]

Morning,

Thanks everyone for your help up until now. I have one last Macro/VBA that I would really do with some help with please! Basically, below is what I need to achieve:

1. In the Workbook, go to the Worksheet called BP
2. Search in the the BP Worksheet for the column called 'Article'
3. If you find the column called 'Article', please select that whole column, and copy and paste it to column 'A' on the Worksheet called BP
   
   ***Please note that there will already data in column 'A'; so when copying and pasting the Article column, I basically need it to insert a new column to do the copy and paste; ie to avoid overwriting the data that is in column A***​

 

[abdelfattah]

please select that whole column, and copy and paste it to column 'A' on the Worksheet called BP

***Please note that there will already data in column 'A'; so when copying and pasting the Article column, I basically need it to insert a new column to do the copy and paste; ie to avoid overwriting the data that is in column A***

Your words are completely contradictory.

 

[carefreeant88]

Your words are completely contradictory.

Please let me know which bit is unclear or needs clarifying?

 

[abdelfattah]

You want copy to column A and you said there is already data in column A , so why you want to copy to column A if you want the old data in column A?!! . you can select any column to avoid that.

 

[carefreeant88]

You want copy to column A and you said there is already data in column A , so why you want to copy to column A if you want the old data in column A?!! . you can select any column to avoid that.

I don't want to copy column A.

I want to copy the column called 'Article'.

And then effectively copy that into Column A.

There is already data in Column A, which I don't want to overwrite. So in essence - I assume - that the Macro would need to Insert a column to the left of Column 'A' in order to put the data I have copied from the column called 'Article'.

 

[abdelfattah]

ok
this is my try if I understand you correctly.

Sub CopyGoods()
    Dim sr As Range

    Set sr = Sheets("BP").Rows(1).Find("Article")

    If sr Is Nothing Then
        MsgBox "Article column does not exists ", vbInformation, "Article word not found"
    Else
       Columns(1).Insert Shift:=xlToLeft

        sr.EntireColumn.Copy Sheets("BP").Range("A1")
    End If
    End Sub

 

[carefreeant88]

ok
this is my try if I understand you correctly.

Sub CopyGoods()
    Dim sr As Range

    Set sr = Sheets("BP").Rows(1).Find("Article")

    If sr Is Nothing Then
        MsgBox "Article column does not exists ", vbInformation, "Article word not found"
    Else
       Columns(1).Insert Shift:=xlToLeft

        sr.EntireColumn.Copy Sheets("BP").Range("A1")
    End If
    End Sub

Thank you!!

 

[abdelfattah]

Your welcome.
and don't forget mark solved if this solved your question.