Realign Columns? Imossible?

[ryguy7272]

I used Ron de Bruin’s ‘merge cells’ macro:
http://www.rondebruin.nl/copy2.htm

I’ve 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 can’t build a Pivot Table
based on this data set. Let’s say Firm Name is in Column A; this is
consistent for all columns, so we’re 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? I’m
thinking this could be quite easy, quite difficult, or just totally
impossible. I can’t think of a solution right now. Does anyone have any
thoughts on the matter?

Regards,
Ryan---

 

[DomThePom]

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]

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:=xlDatabase, 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]

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]

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--

 

[ryguy7272]

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 here:

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---

 

[DomThePom]

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?