Macro to rearrange/add columns based on column header?

  • Thread starter Thread starter drdavidge
  • Start date Start date
D

drdavidge

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 3
columns to a specific order.

2) Add columns before/after specific columns, again based on the heade
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 b
experimenting, but could you guys put me in the right direction? Thi
could possibly be really easy but I am just not sure how to go abou
starting it, specifically the searching and moving/adding columns.

Thanks in advance
 
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?
 
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
 
Back
Top