Macro_Reorder Columnar Data to coincide with range in Column A 2

[MwTV]

Hopefully, this can be done;

Occasionally, I run complex queries and drop the result in Excel templates.

Problem is when the order of columns in the query result do not coincide with the column headings in the Excel templates. Therefore, I have to manually cut and paste the columns in the query result to coincide with the columns in the Excel template.

Question - Is it possible to enter the column headings in column "A" of a worksheet and have a macro or vba to rearrange the columnar data that is in columns B through O so that the columns now coincide with the order of the data in column "A?"

For example, if I have the following entered in column A;

Dog
Cat
Horse

and I have the columns in my query result set like

Cat
Horse
Dog

My desired result would be for the columns in my query result set to be

Dog
Cat
Horse

Thanks in advance.

 

[Gavona]

On a separate sheet list the column hgeadings in your desired order in a single row.
Give this range the name "Extract".

Name the first cell in the data that you have extracted "Mydata".

Use this macro.

Sub TEST()
Range("Mydata").CurrentRegion.AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Range("output"), _
    Unique:=False
End Sub

This can be done without a macro but you would need a dynamic named range to identify your data (I assume the number of rows may change) and Excel is very quirky about filtering the data to a new sheet - can only be done using named ranges and even then you must start from the correct worksheet (I can't recall if that is the source sheet or the extract to sheet). If doing manually then just leave the criteria range blank.

Gavin

 

[MwTV]

I receive a compile error: "Named argument not found"

and the following is highlighted "Action:="

Currently, checking Tools/References.

Any clue as to what I am missing?

 

[Gavona]

Which version of Excel do you have?
CopyTo range should be "Extract" not "Output" (actually it must just be consistent with the name you used. "Extract" is useful because it defaults in when doing things manually.

Gavin

 

[Gavona]

I can't replicate your error on Excel 2003. However the only missing parameter is Criteria. You could try defining a criteria range - just a blank cell.

Have you succesfully defined those named ranges? Check using Insert,Name,Define

Gavin

 

[MwTV]

Using Excel 2002

Error persists.

Currently, have the following range names;
MyData - Cell A2 of data that should be reordered
Output - 13 column worksheet that is currently blank
Extract - Row containing column headings, in desired order

I do not have a "criteria" range name. Will set up a blank area on another worksheet and name "criteria."

How would I incorporate into the code?

What else am I missing?

 

[Gavona]

OK, we will not use "Output", you can delete that sheet if you like.
Use this code:

Sub TEST()
Range("Mydata").CurrentRegion.AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Range("extract"), _
    Unique:=False
End Sub

If the error persits then we can try something else:
1.Cut the extract range and paste onto the same sheet as the data - maybe in columns to the right of your data.
2.Click on a cell in your data, say A2.
3.Switch on the macro-recorder
4.Data,Filter,AdvancedFilter...... Copy to another location.....Leave the criteria blank.

Does that work?
Stop the recorder and post the recorded code.



Gavin

 

[MwTV]

How does the "Advanced Filter" feature reorganize the columns of data so that the column headings and the associated data below coincide with the desired columnar order of data as displayed in the range named "Extract?"

Also, "Extract" is not referenced in the code.

Tinkering around with something similar to the following;

Sub ReorderColumns()

Dim WorkingSheet As Worksheet, NewSheet As Worksheet
Dim DataRange As Range, NewRange As Range
Dim EvalRange1 As String '

Application.ScreenUpdating = False
Set WorkingSheet = ActiveSheet
'Set DataRange = Range("A1:G" & Range("A65536").End(xlUp).Row)
Range("MyData").CurrentRegion.AdvancedFilter _
Sheets.Add
Range("A1").Value = Range("Extract").Offset(1, 1)
Range("B1").Value = Range("Extract").Offset(1, 2)
.
.
.



Just trying to determine if this can actually be performed.

 

[MwTV]

Maybe, a revised example is needed

For example, if I have the following entered in columns A through C of my query result set;

Name---State-----City
Jill---Nevada----LasVegas
Seth---Illinois--Chicago
Mary---Florida----Miami

and I have the columns in my excel template like

Name
City
State

Therefore, my desired result would be for the columns in my query result set to be

Name---City-------State
Jill---LasVegas---Nevada
Seth---Chicago----Illinois
Mary---Miami------Florida

Any insight as to how the vba code should be altered?

Thanks in advance.

 

[Gavona]

How does the "Advanced Filter" feature reorganize the columns of data so that the column headings and the associated data below coincide with the desired columnar order of data as displayed in the range named "Extract?"

Yes, I was mistaken in my first post the code should reference the Range("Extract") not Range("Output"). (Or rather should be consistent with whatever name you have used to specify the column headings in the correct order).
I thought that my later posts corrected that error but I probably was not clear.

Not sure if
Range("A1").Value = Range("Extract").Offset(1, 1)
should be
Range("A1").Value = Range("Extract").Offset(1, 1).value
but I do not see why you cannot hard code the value, i.e.
Range("A1").Value = "Name"

Presumably Range("Mydata") and DataRange are the same?
You don't show how you are using DataRange.

I am assuming that your datarange:
1. is bounded by blank rows and columns
2. does not contain any totally blank rows or columns
3. has a "field heading"/label in a single row at the top of each and every column.

Gavin

 

[SkipVought]

Hi,

Reorder the fields in your QUERY.

faq68-5829

Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}