Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Macro to rearrange/add columns in all the sheets based oncolumn header
Hi,
I need to rearrange columns on many sheets in a certain order based on on column header. For example, my columns might come into my spreadsheet as C,B,D,G,A,H,I,F , or as F,B,C,A,D etc. I want them all sorted say A,B,C,D - the rest of the columns is irrelevant. How do I accomplish this ? TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Macro to rearrange/add columns in all the sheets based oncolumn header
What about the sort function Data/Sort prior to xl2007. I recorded a
macro while doing this: Selection.Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight Presumably you could loop through all the sheets of interest and run this code for each. You'd need to determine the size of your data range in each sheet (or is it always the same?). Post back if this is close to what you were looking for and I'll develop it a bit further. Rgds, Andrew On Feb 17, 1:34*pm, avi wrote: Hi, I need to rearrange columns on many sheets in a certain order based on on column header. For example, my columns might come into my spreadsheet as C,B,D,G,A,H,I,F , or as F,B,C,A,D *etc. I want them all sorted say A,B,C,D - the rest of the columns is irrelevant. How do I accomplish this ? TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Macro to rearrange/add columns in all the sheets based oncolumn header
Andrew, note that I just want to reorder columns C, A, B into A, B, C
not to sort data in columns. There is a similar post on this group - macro "Sub blah()" ; that's what I want but not on a new sheet but I want to be able to run it across all the existing sheets. Antonio |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Macro to rearrange/add columns in all the sheets basedon column header
Since i do not know if you have any unique identification of columns,
I'll assume that in first cell (row 1) there are letters for columns... Sub MoveandCountColumns() 10 For i = 1 To 254 'As 255 is maximum number of columns If Len(Cells(1, i).Value) = 0 Then Exit For 'This is end of columns If Columns(Cells(1, i).Value).Column < i Then Columns(Cells(1, i).Value).Select Selection.Copy Columns(255).Select ActiveSheet.Paste Columns(i).Select Selection.Copy Columns(Cells(1, i).Value).Select ActiveSheet.Paste Columns(255).Select Selection.Copy Columns(i).Select ActiveSheet.Paste GoTo 10 End If Next I'm usind column 255 as temporary column for data that should be removed. I'll try to explain... If in first column you have C and in third you have E then i'll copy E column to column 255 then copy first column to its right place and then put back column 255 content in first columns... before macro i had C A E, after i have E X C, what means that i C is in right place so i start from begin again until all columns are in right places. Here you will need some loops to identify what column on what place you will need. If you can provide On 17.02.2010 14:34, avi wrote: Hi, I need to rearrange columns on many sheets in a certain order based on on column header. For example, my columns might come into my spreadsheet as C,B,D,G,A,H,I,F , or as F,B,C,A,D etc. I want them all sorted say A,B,C,D - the rest of the columns is irrelevant. How do I accomplish this ? TIA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Macro to rearrange/add columns in all the sheets based oncolumn header
Hi Monarch,
Since i do not know if you have any unique identification of columns, I'll assume that in first cell (row 1) there are letters for columns... Answer to your query is YES. ROW 1 contains unique identification of columns. I want to arrange couple of columns across all the sheets in the same way since I have them now randomly placed across sheets in different columns. Namely these a "NUM", "SYS", "DIA", "TAG" etc... Your code works perfectly on one sheet where 1st row (as you said) has letters but can you help me with this. TIA |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Macro to rearrange/add columns in all the sheets basedon column header
Ok, here is solution
Sub MoveandCountColumnsA() 10 For i = 1 To 254 'As 255 is maximum number of columns If Len(Cells(1, i).Value) = 0 Then Exit For 'This is end of columns col = ColumnPlace(Cells(1, i).Value, i) If col < i Then Columns(col).Select Selection.Copy Columns(255).Select ActiveSheet.Paste Columns(i).Select Selection.Copy Columns(col).Select ActiveSheet.Paste Columns(255).Select Selection.Copy Columns(i).Select ActiveSheet.Paste GoTo 10 End If Next End Sub and ColumnPlace Function. This function determine what is column identifier and in which column should it move data. You can add in CASE what is column header and where you wish it to put that column. Do not chage "Case else" part... Public Function ColumnPlace(ColumnName, CurrColumn) Select Case ColumnName Case "NUM" ColumnPlace = 1 Case "SYS" ColumnPlace = 2 Case "DIA" ColumnPlace = 3 Case "TAG" ColumnPlace = 4 Case "VAR2" ColumnPlace = 5 Case Else ColumnPlace = CurrColumn End Select End Function This code will move every column on its needed place only if that column is mentioned in ColumnPlace function On 17.02.2010 18:24, avi wrote: Hi Monarch, I want to arrange couple of columns across all the sheets in the same way since I have them now randomly placed across sheets in different columns. Namely these a "NUM", "SYS", "DIA", "TAG" etc... Your code works perfectly on one sheet where 1st row (as you said) has letters but can you help me with this. TIA |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Macro to rearrange/add columns in all the sheets based oncolumn header
Hi,
While I was waiting on a reply, I modified the code found on http://www.pcreview.co.uk/forums/thread-2587219.php in such way that I am pasting copied columns from clipboard by inserting it to the left of the first column (A). It also works but I can't manage to run it across all the sheets in workbook. Note: there is a limitation in my code (as far I can see) that first column in all the sheets needs to have the same header. Otherwise, if the first column is to be pasted on the left of it - there's error. (I don't have code with me now, but will post it next week when I am back at work. Basically it is very close to the code on the link above )... Luckily, all my sheets start with the same header in column A so I am able to arrange it. The code you posted ( thanx very much for it ) works perfectly on one sheet. However there is also limitation that there shall be no blank column between columns of interest ie if there is blank on between, say "NUM" & "SYS" nothing happens. Also, please note that I wanted to run it across all the sheets in a workbook. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Macro to rearrange/add columns in all the sheets based oncolumn header
Well there is also another option as advised by Ax on Dbforums to use
Data|Sort, Options and Under Orientation, select "Sort left to right". The only thing needed here is Custom List that defines desired columns order. (see below OrderCustom:=6) ---------------------- Sub Macro1() Cells.Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=6, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal End Sub ---------------------- If only I could run this for all the sheets, it is then solved... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Macro to rearrange/add columns in all the sheets basedon column header
here is complete solution
Does not stop on blank cell (it stops if there are 3 (userdefined) blank cell in a row) If works on all sheets in a workbook Custom list can be arranged in ColumnPlace function (just insert new case line with whatever content of column and desired place of column here is code: Sub MoveandCountColumnsA() Static CountBlanks 'Next two lines cycle thru worksheets For Each a In Worksheets a.Activate 10 For i = 1 To 254 'As 255 is maximum number of columns If Len(Cells(1, i).Value) = 0 Then CountBlanks = CountBlanks + 1 'If there is 3 columns in a row that was blank assume last column If CountBlanks 3 Then '3 blanks in a row, assume we are over last column Exit For End If Else 'Reset counter if there is only 1 or 2 blank cells CountBlanks = 0 End If col = ColumnPlace(Cells(1, i).Value, i) If col < i Then Columns(col).Select Selection.Copy Columns(255).Select ActiveSheet.Paste Columns(i).Select Selection.Copy Columns(col).Select ActiveSheet.Paste Columns(255).Select Selection.Copy Columns(i).Select ActiveSheet.Paste GoTo 10 End If Next Next End Sub Public Function ColumnPlace(ColumnName, CurrColumn) Select Case ColumnName Case "NUM" 'Put here content of first cell of column ColumnPlace = 1 'Put here desired place of column Case "SYS" ColumnPlace = 2 Case "DIA" ColumnPlace = 3 Case "TAG" ColumnPlace = 7 Case "VAR2" ColumnPlace = 5 Case Else ColumnPlace = CurrColumn End Select End Function And avi, you are welcome, if there is anything you would like me to change, just ask :) On 18.02.2010 19:29, avi wrote: Hi, While I was waiting on a reply, I modified the code found on http://www.pcreview.co.uk/forums/thread-2587219.php in such way that I am pasting copied columns from clipboard by inserting it to the left of the first column (A). It also works but I can't manage to run it across all the sheets in workbook. Note: there is a limitation in my code (as far I can see) that first column in all the sheets needs to have the same header. Otherwise, if the first column is to be pasted on the left of it - there's error. (I don't have code with me now, but will post it next week when I am back at work. Basically it is very close to the code on the link above )... Luckily, all my sheets start with the same header in column A so I am able to arrange it. The code you posted ( thanx very much for it ) works perfectly on one sheet. However there is also limitation that there shall be no blank column between columns of interest ie if there is blank on between, say "NUM" & "SYS" nothing happens. Also, please note that I wanted to run it across all the sheets in a workbook. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel - Macro to rearrange/add columns in all the sheets based oncolumn header
Well, all I can say: Thanx very much Monarch!
Finally I can rearrange it now... :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rearrange columns | Excel Programming | |||
Macro to rearrange/add columns based on column header? | Excel Programming | |||
Rearrange columns using VBA | Excel Programming | |||
Macro help to rearrange excel table | Excel Programming | |||
hide columns based on header macro | Excel Programming |