How to select entire column in Excel by custom column header using VBA. RRS feed

  • Question

  • Hi.

    I am new to VBA. I have a requirement to select and copy entire column,  where column header name as Record Id. I have other columns also.

    I want to copy this column (Record Id) from Call Activity Sheet and paste it to a different workbook.

    Note: This column position is not fix in the excel. It varies the position. 


    I would thankfull if anyone help me out.

    Monday, June 1, 2015 7:34 PM


All replies

  • Hello Sakti,

    The most easiest way is to try using the Excel Macro Recorder. See Create or delete a macro . The required code will be generated for you in the background.

    Monday, June 1, 2015 9:23 PM
  • Hi

    Try this :

    Sub find_col()
    Dim wbpath As String
      With Worksheets("Sheet1")  '>>Change the sheet name if needed.
        .Rows(1).Find("Record ID", , xlValues, xlWhole) _
       End With
      wbpath = "c:\Users\John\Desktop\"   '>>Change the path to your Destination workbook
    Workbooks.Open Filename:=wbpath & "Destination.xls" '>>Change the name of your file with proper 'extension (xlsm)
    Worksheets("Sheet1").Range("A2").PasteSpecial  '>>Change the location if needed
    End Sub

    This macro will find your column "RecordID", open your destination workbook and paste the data starting at A2.

    Make changes to your needs.


    • Edited by Cimjet Tuesday, June 2, 2015 1:23 AM
    • Proposed as answer by Cimjet Tuesday, June 9, 2015 1:16 PM
    Tuesday, June 2, 2015 1:16 AM