Copy Selected Columns to Same Sheet

[egavasrg]

Hi All:

I have a spreadsheet that has all the data I need but the column headings are not in the right order to transmit to the vendor. I am trying to copy and paste the columns I need to a range in the same sheet called "FORMAT". I started building this macro based on info from the net but I get a "Type Mismatch" error when I run it. I need the macro to find the column headers and then copy the data in that column to the new range. The code I have so far is below. Thanks for any help.

Dim Cell        As Range
Dim iCol        As Integer
Set Cell = Sheets("Import").Range("IMPORT").Find("LNAME", "FNAME", "GENDER", "ENTITY") '/These are the column headers

    If Cell Is Nothing Then
        MsgBox "Unable to fin Header"
        Exit Sub
    
    Else
        iCol = Cell.Column
End If

With Sheets("Import")
    .Range(Cells(1, iCol).Cell(Rows.Count, iCol).End(xlUp)).CopytoRange = ("FORMAT")
    
End With
   
End Sub

[arlu1201]

There is something wrong with the declaration statement -

Set Cell = Sheets("Import").Range("IMPORT").Find("LNAME", "FNAME", "GENDER", "ENTITY") '/These are the column headers

I am not sure you can do it this way.
Can you attach a sample workbook with the details of what you need copied and to where? Maybe someone here can help you with an alternate code.

[egavasrg]

Hi Arlu1201. I think I goofed on the tagging and my post with the attachment was deleted by an administrator. I'm really new here so I'm still learning all the nuances. Here is the file with the sample data. Any help is appreciated. I want to post the columns I listed starting at O5 in this same sheet. Thanks.

rgerard

[arlu1201]

Please provide the details on what data should be copied to which headers.

[egavasrg]

Hi Arlu1201. I posted the spreadsheet so you can see the order of the columns as they come in. I need to reset them into the following column order: LNAME, FNAME, YEAR (XXXX), SSN, ENTITY. I want to post all data from those original columns into new columns with the same headings in range O5:S5 and Hope this helps. Thanks.

[arlu1201]

I am sure they will be better ways of doing this (considering all the experts we have here) but this is one of them:

Sub arrange_cols()

Rows("5:5").Select
Cells.Find(What:="LNAME", After:=ActiveCell, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, searchformat:=False).Activate
cellcol = ActiveCell.Column

Range(Cells(5, cellcol), Cells(5, cellcol).End(xlDown)).Copy
Range("O5").Select
ActiveSheet.Paste


Rows("5:5").Select
Cells.Find(What:="FNAME", After:=ActiveCell, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, searchformat:=False).Activate
cellcol = ActiveCell.Column

Range(Cells(5, cellcol), Cells(5, cellcol).End(xlDown)).Copy
Range("P5").Select
ActiveSheet.Paste


Rows("5:5").Select
Cells.Find(What:="YEAR  (####)", After:=ActiveCell, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, searchformat:=False).Activate
cellcol = ActiveCell.Column

Range(Cells(5, cellcol), Cells(5, cellcol).End(xlDown)).Copy
Range("Q5").Select
ActiveSheet.Paste


Rows("5:5").Select
Cells.Find(What:="SSN", After:=ActiveCell, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, searchformat:=False).Activate
cellcol = ActiveCell.Column

Range(Cells(5, cellcol), Cells(5, cellcol).End(xlDown)).Copy
Range("R5").Select
ActiveSheet.Paste

Rows("5:5").Select
Cells.Find(What:="ENTITY", After:=ActiveCell, LookIn:=xlValues, Lookat:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, searchformat:=False).Activate
cellcol = ActiveCell.Column

Range(Cells(5, cellcol), Cells(5, cellcol).End(xlDown)).Copy
Range("S5").Select
ActiveSheet.Paste

Columns("O:S").AutoFit

End Sub

[egavasrg]

Hi Arlu1201. I'll try this out. Just from reading it, it looks like it will work. I like the FIND function. It's one of the few parts of VBA that I understand. Like you said, there may be a more elegant way to do this, but your code will get it done I think. I'll get back to you as soon as I run it. By the way, do you ever sleep :-).

[egavasrg]

Hi Arlu.

I just dimensioned cellcol as Integer and the program worked!!! Thank you so very much for you help. I really appreciate all the effort you put into this. Thank you.

rgerard

[arlu1201]

I do sleep when you are awake, since you are in US and i am in India.

I guess you have "Option Explicit" mentioned at the top of your code, which makes it mandatory for all variables to be defined. Its a good way of coding, however i had to do something quick so didnt define the variables.

Incase cellcol gives you an error (since you have defined it as integer) you can then change it to Long. (Dim cellcol as long).

[egavasrg]

Hi Arlu. I inserted the code into a module and ran it. I got an error. When I step through the code with F8, I get the following error when it reaches the first line that reads "cellcol = ActiveCell.Column".

Compile error:
Variable not defined

I tried to dimension the variable cellcol as range = to ActiveCell.Column, but that didn't work. I think I mentioned that I'm very new to this so I'm not sure what to do next. Any help you can provide is appreciated. Thanks.

rgerart