Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 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 -- drdavidg ----------------------------------------------------------------------- drdavidge's Profile: http://www.excelforum.com/member.php...fo&userid=3616 View this thread: http://www.excelforum.com/showthread.php?threadid=55947 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to rearrange/add columns based on column header?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to rearrange/add columns based on column header?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rearrange multiple columns under one column | Excel Discussion (Misc queries) | |||
Help, please - How to rearrange 1 column of data into 4 columns ? | Excel Discussion (Misc queries) | |||
how do I rearrange a column into a series of columns? | Excel Programming | |||
hide columns based on header macro | Excel Programming | |||
macro to hide column based on header | Excel Programming |