none
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

Answers

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) _
        .EntireColumn.SpecialCells(xlCellTypeConstants).Offset(1).SpecialCells(xlCellTypeConstants).Copy
       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.


    Cimjet


    • 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