VBA_Reorder Columns 3

[MwTV]

Using Excel 2002 and occasionally 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. (Note, some of the query results are over 30,000 records) Then, I copy the query result to the Excel Template.

Question - Is it possible to enter the column headings (in the desired order using Excel copy/transpose feature) in column "A" of the worksheet that contains the query results and have a macro or vba to rearrange the query result data that is in columns “B” through “D” so that the columns now coincide with the order of the data in my Excel template?

For example, if I have the following entered in columns “B” through “D” of my query result set;

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

and the columns in my excel template are

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


Can this be done?

Thanks in advance.

 

[AnotherHiggins]

First, why don't you just reorder the columns in Access? That would be a lot simpler.

Second, for 30,000 records, there are better ways to do this than copy'n'paste. You can export from Access or, better yet, create pivot table or whatever you are doing with the 30,000 by getting external data. Excel can use source data that isn't in the active workbook - even directly out of Access.

Lastly, please post VBA / Macro-specific questions in forum707

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

Copy 'n' paste should be used sparingly, IMHO.

If you are running queries, just change the column order in the query, being sure to UNCHECK the checkbox in Data Range Properties, Preserve column sort/filter/layout

You can have the column is whatever order you specify in your query.

Skip,

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

 

[MwTV]

Revising the order of the data elements within the queries in MS Access was my initial option.

Originally, the queries were in BI Query Format that is used to access data within a SQL Server database. I converted them to passthru queries in MS Access 2002.

There are 65 MS Access queries and 65 Excel Templates.

Reviewing and revising each of the 65 queries to coincide with the order within the Excel templates would be quite laborious but could solve the problem until another employee comes along and changes the order of the sql script data elements.

Therefore, I usually just copy the column headings from the Excel template into column "A" of the worksheet that contains the query results and manually cut and paste the columns to get them in the order desired.

 

[combo]

For regular use it is better to redesign the query.
Pure excel way to manually reorder columns (or rows) in excel: select the column and, holding the SHIFT key, drag into destination position.

combo

 

[SkipVought]

"There are 65 MS Access queries and 65 Excel Templates."

"Therefore, I usually just copy the column headings from the Excel template into column "A" of the worksheet that contains the query results and manually cut and paste the columns to get them in the order desired."

Pay it now or pay it later!!!

Skip,

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