Macro to rearrange/add columns based on column header?

Hey guys, I am trying to create a macro for a huge data file of 200+ columns. I basically want it to do a few things:

1) Search though the file for certain column headers and move about 30 columns to a specific order.

2) Add columns before/after specific columns, again based on the header cell value.

3) Populate these new extra columns with formulas to generate values.


I know basics of VBA and can usually figure out things on my own by experimenting, but could you guys put me in the right direction? This could possibly be really easy but I am just not sure how to go about starting it, specifically the searching and moving/adding columns.

Thanks in advance.

To rearrange columns only,

Insert row1
number row1 from 10 to 2000 step 10
renumber column 50 as 91 if you want column 50 to appear after column
90
renumber column 2010 as 121 if you want column 120 followed with a
blank column
etc
sort all columns left to right

drdavidge wrote:
> I actually kinda figured this out from some other posts on here, but not
> completely. Heres the code:
>
>
> Code:
> --------------------
>
> Sub blah()
>
> Dim rng As Range
> Dim current_sheet
> current_sheet = ActiveSheet.Name
> Worksheets.Add().Name = "ReArranged"
> Sheets(current_sheet).Select
>
> For Each rng In Range("A1:IV1")
> If rng.Value = "Facility ID" Then
> rng.EntireColumn.Copy
> Sheets("ReArranged").Select
> Columns("E:E").Select
> ActiveSheet.Paste
> Sheets(current_sheet).Select
> End If
> Next rng
>
>
> End Sub
>
> --------------------
>
>
> This works great, but I am going to have to search for about 30-40
> columns and copying and pasting this over and over seems to be a little
> redundant. Is there some way I can setup an array that has the columns
> header i am looking for (for example, in this function it is "Facility
> ID") and the column letter it should go in (here it is column "E") ? I
> have a general idea but I have no idea how to translate it into excel
> vba terms.
>
> So basically it seems like I need it to search the column headers for a
> name in an array, and if found, put it in the column that corresponds to
> that specific header.
>
> I was thinking that I could maybe make the "array" as an excel sheet
> and use a VLOOKUP to find out which column to paste into. But again, I
> am not sure how to code this. Is there any easier ways?
>
>
> --
> drdavidge
> ------------------------------------------------------------------------
> drdavidge's Profile: http://www.excelforum.com/member.php...o&userid=36168
> View this thread: http://www.excelforum.com/showthread...hreadid=559477