Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Realign Columns? Imossible?
I used Ron de Bruins €˜merge cells macro:
http://www.rondebruin.nl/copy2.htm Ive used it multiple times and the macro itself works great €“ everything is copied to one summary sheet, named MergeSheet. Great!!! I have one small problem now (which I never encountered before). If the data in the some of the columns in the different sheets is not in sync, the data in some of the columns of the MergeSheet is not in sync€¦and I cant build a Pivot Table based on this data set. Lets say Firm Name is in Column A; this is consistent for all columns, so were ok here. However, when I move further to the right, Column G of MergeSheet has €˜Contact Person (because this is the way it is on one sheet) and Column G of MergeSheet also has €˜Phone Number (because this is the way it is on another sheet)!!! Is there a way to get Excel to line up all the columns that have the same heading? Im thinking this could be quite easy, quite difficult, or just totally impossible. I cant think of a solution right now. Does anyone have any thoughts on the matter? Regards, Ryan--- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Realign Columns? Imossible?
Just sort the data on each sheet by column header before copying it (assumes
that all data sheets have same number of columns and same column headers just in a different order) sort method of range object Usin g same object names from Rons code gives sh.cells(1,1).currentregion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal "ryguy7272" wrote: I used Ron de Bruins €˜merge cells macro: http://www.rondebruin.nl/copy2.htm Ive used it multiple times and the macro itself works great €“ everything is copied to one summary sheet, named MergeSheet. Great!!! I have one small problem now (which I never encountered before). If the data in the some of the columns in the different sheets is not in sync, the data in some of the columns of the MergeSheet is not in sync€¦and I cant build a Pivot Table based on this data set. Lets say Firm Name is in Column A; this is consistent for all columns, so were ok here. However, when I move further to the right, Column G of MergeSheet has €˜Contact Person (because this is the way it is on one sheet) and Column G of MergeSheet also has €˜Phone Number (because this is the way it is on another sheet)!!! Is there a way to get Excel to line up all the columns that have the same heading? Im thinking this could be quite easy, quite difficult, or just totally impossible. I cant think of a solution right now. Does anyone have any thoughts on the matter? Regards, Ryan--- -- RyGuy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Realign Columns? Imossible?
Clever! I only wish it was that easy. The columns are mostly the same on
all of the sheets, but some sheets have more columns with more data and some have less with less data, and it is these slight differences that are causing me big problems. Perhaps I should build a Pivot Table on each page and then extract the defined elements from each PT. Any thoughts on how this would work? What would the code be like to build a PT on multiple pages? Pseudo Loop: For Each sh In Worksheets Range("A1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "MergeSheet!R1C1:R200C26").CreatePivotTable TableDestination:="", TableName _ :="PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(205, 1) Next sh Is that close? Regards, Ryan-- RyGuy "DomThePom" wrote: Just sort the data on each sheet by column header before copying it (assumes that all data sheets have same number of columns and same column headers just in a different order) sort method of range object Usin g same object names from Rons code gives sh.cells(1,1).currentregion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal "ryguy7272" wrote: I used Ron de Bruins €˜merge cells macro: http://www.rondebruin.nl/copy2.htm Ive used it multiple times and the macro itself works great €“ everything is copied to one summary sheet, named MergeSheet. Great!!! I have one small problem now (which I never encountered before). If the data in the some of the columns in the different sheets is not in sync, the data in some of the columns of the MergeSheet is not in sync€¦and I cant build a Pivot Table based on this data set. Lets say Firm Name is in Column A; this is consistent for all columns, so were ok here. However, when I move further to the right, Column G of MergeSheet has €˜Contact Person (because this is the way it is on one sheet) and Column G of MergeSheet also has €˜Phone Number (because this is the way it is on another sheet)!!! Is there a way to get Excel to line up all the columns that have the same heading? Im thinking this could be quite easy, quite difficult, or just totally impossible. I cant think of a solution right now. Does anyone have any thoughts on the matter? Regards, Ryan--- -- RyGuy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Realign Columns? Imossible?
Ok - not sure about your code - seems too complex
All you need do is: 1. Create an array of a complete list of column names that you require to be in each sheet 2. Run though the work book sheet by sheet 3. For each sheet: a)order columns as suggested below b)run though each required column and if column does not exist in sheet then insert an empty one "ryguy7272" wrote: Clever! I only wish it was that easy. The columns are mostly the same on all of the sheets, but some sheets have more columns with more data and some have less with less data, and it is these slight differences that are causing me big problems. Perhaps I should build a Pivot Table on each page and then extract the defined elements from each PT. Any thoughts on how this would work? What would the code be like to build a PT on multiple pages? Pseudo Loop: For Each sh In Worksheets Range("A1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "MergeSheet!R1C1:R200C26").CreatePivotTable TableDestination:="", TableName _ :="PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(205, 1) Next sh Is that close? Regards, Ryan-- RyGuy "DomThePom" wrote: Just sort the data on each sheet by column header before copying it (assumes that all data sheets have same number of columns and same column headers just in a different order) sort method of range object Usin g same object names from Rons code gives sh.cells(1,1).currentregion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal "ryguy7272" wrote: I used Ron de Bruins €˜merge cells macro: http://www.rondebruin.nl/copy2.htm Ive used it multiple times and the macro itself works great €“ everything is copied to one summary sheet, named MergeSheet. Great!!! I have one small problem now (which I never encountered before). If the data in the some of the columns in the different sheets is not in sync, the data in some of the columns of the MergeSheet is not in sync€¦and I cant build a Pivot Table based on this data set. Lets say Firm Name is in Column A; this is consistent for all columns, so were ok here. However, when I move further to the right, Column G of MergeSheet has €˜Contact Person (because this is the way it is on one sheet) and Column G of MergeSheet also has €˜Phone Number (because this is the way it is on another sheet)!!! Is there a way to get Excel to line up all the columns that have the same heading? Im thinking this could be quite easy, quite difficult, or just totally impossible. I cant think of a solution right now. Does anyone have any thoughts on the matter? Regards, Ryan--- -- RyGuy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Realign Columns? Imossible?
I have a whole new outlook on this project now, thanks to DomThePom. I
absolutely believe this will work, but I'm still 1 or 2 steps away from a solution. The sheets had a bunch of filters applied, and I don't think you can sort left:right with the sorting tool applied, so I went through each sheet to disable this feature (code below). That works fine, but there are still two issues. 1) There is a feature (new to Excel 2003 I think) called List (Data List Create List). I can't seem to disable this feature, and having it on is preventing me from sorting left:right in a few sheets. Does anyone know how to disable this List feature? 2) I love the idea of creating an array and then looping through each sheet, etc. How can I set this up? My creative vision seems stymied this morning. I have the following (just a small sample) in the following columns: A = Hotel Information B = Company_Address C = City D = State E = Zipcode F = Phone G = Number of Rooms It was suggested that I: a)order columns as suggested below b)run though each required column and if column does not exist in sheet then insert an empty one How can I do that??? I'm using this code to sort Left:Right: Sub Macro1() For Each sh In ThisWorkbook.Worksheets If ActiveSheet.AutoFilterMode = True Then Rows("1:1").Select Selection.AutoFilter If ActiveSheet.AutoFilterMode = False Then End If End If Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub Thanks, in advance, for everything, Ryan-- -- RyGuy "DomThePom" wrote: Ok - not sure about your code - seems too complex All you need do is: 1. Create an array of a complete list of column names that you require to be in each sheet 2. Run though the work book sheet by sheet 3. For each sheet: a)order columns as suggested below b)run though each required column and if column does not exist in sheet then insert an empty one "ryguy7272" wrote: Clever! I only wish it was that easy. The columns are mostly the same on all of the sheets, but some sheets have more columns with more data and some have less with less data, and it is these slight differences that are causing me big problems. Perhaps I should build a Pivot Table on each page and then extract the defined elements from each PT. Any thoughts on how this would work? What would the code be like to build a PT on multiple pages? Pseudo Loop: For Each sh In Worksheets Range("A1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "MergeSheet!R1C1:R200C26").CreatePivotTable TableDestination:="", TableName _ :="PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(205, 1) Next sh Is that close? Regards, Ryan-- RyGuy "DomThePom" wrote: Just sort the data on each sheet by column header before copying it (assumes that all data sheets have same number of columns and same column headers just in a different order) sort method of range object Usin g same object names from Rons code gives sh.cells(1,1).currentregion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal "ryguy7272" wrote: I used Ron de Bruins €˜merge cells macro: http://www.rondebruin.nl/copy2.htm Ive used it multiple times and the macro itself works great €“ everything is copied to one summary sheet, named MergeSheet. Great!!! I have one small problem now (which I never encountered before). If the data in the some of the columns in the different sheets is not in sync, the data in some of the columns of the MergeSheet is not in sync€¦and I cant build a Pivot Table based on this data set. Lets say Firm Name is in Column A; this is consistent for all columns, so were ok here. However, when I move further to the right, Column G of MergeSheet has €˜Contact Person (because this is the way it is on one sheet) and Column G of MergeSheet also has €˜Phone Number (because this is the way it is on another sheet)!!! Is there a way to get Excel to line up all the columns that have the same heading? Im thinking this could be quite easy, quite difficult, or just totally impossible. I cant think of a solution right now. Does anyone have any thoughts on the matter? Regards, Ryan--- -- RyGuy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Realign Columns? Imossible?
I just found out that these files probably will come through with the columns
in sync (fingers crossed). Nevertheless, I am curious to see the code for the solution you proposed Dom. You said it would be something like this: For each sheet: a)order columns as suggested below b)run though each required column and if column does not exist in sheet then insert an empty one I have part of the loop he For Each sh In ActiveWorkbook.Worksheets sh.Select Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next I don't know what else goes inside the loop though. I believe this is the part 'a', but what about part 'b'? Dom, please send me some code if you know what need to be done to get this working. I would like to learn tis solution and save it for some future (inevitable) circumstance. Regards, Ryan--- -- RyGuy "ryguy7272" wrote: I have a whole new outlook on this project now, thanks to DomThePom. I absolutely believe this will work, but I'm still 1 or 2 steps away from a solution. The sheets had a bunch of filters applied, and I don't think you can sort left:right with the sorting tool applied, so I went through each sheet to disable this feature (code below). That works fine, but there are still two issues. 1) There is a feature (new to Excel 2003 I think) called List (Data List Create List). I can't seem to disable this feature, and having it on is preventing me from sorting left:right in a few sheets. Does anyone know how to disable this List feature? 2) I love the idea of creating an array and then looping through each sheet, etc. How can I set this up? My creative vision seems stymied this morning. I have the following (just a small sample) in the following columns: A = Hotel Information B = Company_Address C = City D = State E = Zipcode F = Phone G = Number of Rooms It was suggested that I: a)order columns as suggested below b)run though each required column and if column does not exist in sheet then insert an empty one How can I do that??? I'm using this code to sort Left:Right: Sub Macro1() For Each sh In ThisWorkbook.Worksheets If ActiveSheet.AutoFilterMode = True Then Rows("1:1").Select Selection.AutoFilter If ActiveSheet.AutoFilterMode = False Then End If End If Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub Thanks, in advance, for everything, Ryan-- -- RyGuy "DomThePom" wrote: Ok - not sure about your code - seems too complex All you need do is: 1. Create an array of a complete list of column names that you require to be in each sheet 2. Run though the work book sheet by sheet 3. For each sheet: a)order columns as suggested below b)run though each required column and if column does not exist in sheet then insert an empty one "ryguy7272" wrote: Clever! I only wish it was that easy. The columns are mostly the same on all of the sheets, but some sheets have more columns with more data and some have less with less data, and it is these slight differences that are causing me big problems. Perhaps I should build a Pivot Table on each page and then extract the defined elements from each PT. Any thoughts on how this would work? What would the code be like to build a PT on multiple pages? Pseudo Loop: For Each sh In Worksheets Range("A1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "MergeSheet!R1C1:R200C26").CreatePivotTable TableDestination:="", TableName _ :="PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(205, 1) Next sh Is that close? Regards, Ryan-- RyGuy "DomThePom" wrote: Just sort the data on each sheet by column header before copying it (assumes that all data sheets have same number of columns and same column headers just in a different order) sort method of range object Usin g same object names from Rons code gives sh.cells(1,1).currentregion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal "ryguy7272" wrote: I used Ron de Bruins €˜merge cells macro: http://www.rondebruin.nl/copy2.htm Ive used it multiple times and the macro itself works great €“ everything is copied to one summary sheet, named MergeSheet. Great!!! I have one small problem now (which I never encountered before). If the data in the some of the columns in the different sheets is not in sync, the data in some of the columns of the MergeSheet is not in sync€¦and I cant build a Pivot Table based on this data set. Lets say Firm Name is in Column A; this is consistent for all columns, so were ok here. However, when I move further to the right, Column G of MergeSheet has €˜Contact Person (because this is the way it is on one sheet) and Column G of MergeSheet also has €˜Phone Number (because this is the way it is on another sheet)!!! Is there a way to get Excel to line up all the columns that have the same heading? Im thinking this could be quite easy, quite difficult, or just totally impossible. I cant think of a solution right now. Does anyone have any thoughts on the matter? Regards, Ryan--- -- RyGuy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Realign Columns? Imossible?
Hi ryguy7272
I have put together an example for you but as it has userforms I cannot really copy it into this post - do you have an email I can send it to? "ryguy7272" wrote: I just found out that these files probably will come through with the columns in sync (fingers crossed). Nevertheless, I am curious to see the code for the solution you proposed Dom. You said it would be something like this: For each sheet: a)order columns as suggested below b)run though each required column and if column does not exist in sheet then insert an empty one I have part of the loop he For Each sh In ActiveWorkbook.Worksheets sh.Select Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next I don't know what else goes inside the loop though. I believe this is the part 'a', but what about part 'b'? Dom, please send me some code if you know what need to be done to get this working. I would like to learn tis solution and save it for some future (inevitable) circumstance. Regards, Ryan--- -- RyGuy "ryguy7272" wrote: I have a whole new outlook on this project now, thanks to DomThePom. I absolutely believe this will work, but I'm still 1 or 2 steps away from a solution. The sheets had a bunch of filters applied, and I don't think you can sort left:right with the sorting tool applied, so I went through each sheet to disable this feature (code below). That works fine, but there are still two issues. 1) There is a feature (new to Excel 2003 I think) called List (Data List Create List). I can't seem to disable this feature, and having it on is preventing me from sorting left:right in a few sheets. Does anyone know how to disable this List feature? 2) I love the idea of creating an array and then looping through each sheet, etc. How can I set this up? My creative vision seems stymied this morning. I have the following (just a small sample) in the following columns: A = Hotel Information B = Company_Address C = City D = State E = Zipcode F = Phone G = Number of Rooms It was suggested that I: a)order columns as suggested below b)run though each required column and if column does not exist in sheet then insert an empty one How can I do that??? I'm using this code to sort Left:Right: Sub Macro1() For Each sh In ThisWorkbook.Worksheets If ActiveSheet.AutoFilterMode = True Then Rows("1:1").Select Selection.AutoFilter If ActiveSheet.AutoFilterMode = False Then End If End If Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal Next End Sub Thanks, in advance, for everything, Ryan-- -- RyGuy "DomThePom" wrote: Ok - not sure about your code - seems too complex All you need do is: 1. Create an array of a complete list of column names that you require to be in each sheet 2. Run though the work book sheet by sheet 3. For each sheet: a)order columns as suggested below b)run though each required column and if column does not exist in sheet then insert an empty one "ryguy7272" wrote: Clever! I only wish it was that easy. The columns are mostly the same on all of the sheets, but some sheets have more columns with more data and some have less with less data, and it is these slight differences that are causing me big problems. Perhaps I should build a Pivot Table on each page and then extract the defined elements from each PT. Any thoughts on how this would work? What would the code be like to build a PT on multiple pages? Pseudo Loop: For Each sh In Worksheets Range("A1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "MergeSheet!R1C1:R200C26").CreatePivotTable TableDestination:="", TableName _ :="PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(205, 1) Next sh Is that close? Regards, Ryan-- RyGuy "DomThePom" wrote: Just sort the data on each sheet by column header before copying it (assumes that all data sheets have same number of columns and same column headers just in a different order) sort method of range object Usin g same object names from Rons code gives sh.cells(1,1).currentregion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _ DataOption1:=xlSortNormal "ryguy7272" wrote: I used Ron de Bruins €˜merge cells macro: http://www.rondebruin.nl/copy2.htm Ive used it multiple times and the macro itself works great €“ everything is copied to one summary sheet, named MergeSheet. Great!!! I have one small problem now (which I never encountered before). If the data in the some of the columns in the different sheets is not in sync, the data in some of the columns of the MergeSheet is not in sync€¦and I cant build a Pivot Table based on this data set. Lets say Firm Name is in Column A; this is consistent for all columns, so were ok here. However, when I move further to the right, Column G of MergeSheet has €˜Contact Person (because this is the way it is on one sheet) and Column G of MergeSheet also has €˜Phone Number (because this is the way it is on another sheet)!!! Is there a way to get Excel to line up all the columns that have the same heading? Im thinking this could be quite easy, quite difficult, or just totally impossible. I cant think of a solution right now. Does anyone have any thoughts on the matter? Regards, Ryan--- -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I realign all data labels in a chart at once | Charts and Charting in Excel | |||
Excel 2002: How to realign data table ? | Excel Discussion (Misc queries) | |||
create a macro to delete columns and then border remaining columns | Excel Programming | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) | |||
Combine multiple columns into two long columns, Repeating rows in first column | Excel Discussion (Misc queries) |