Macro To Organize Header Columns

[gaspower]

Hello,
I would appreciate some help on a Macro that organizes my columns. The excel files I receive all have column headers and contain below A-P, but the headers may not come in the order as below, meaning A may equal IS_Color, F may equal Title. I would like to have the Macro to arrange columns so they match the order as below, column A=Title, B=Fixed Price etc. Also these column headers all have data below them, so can the corresponding data also be moved to keep same data under the correct header. The rows below the columns headers may range, could be 10 rows, may be 30 rows, so if the Macro can read the amount of data and end with the correct number of rows.

A=Title
B=Fixed Price
C=SKU
D=Cond. Descr.
E=Description
F=IS_Brand
G=IS_Model
H=IS_Color
I=IS_Pattern
J=IS_Original/Reproduction
K=IS_Object Type
L=IS_UPC
M=IS_Main Color
N=IS_Size
O=IS_Service
P=Pictures

Possible one Excel file I may receive, random columns:
Capture_Original.JPG

Below is how I would like columns to arrange, no matter what column sort order I may receive the original file.
Capture.JPG

Thank you very much, JR

[PaulM100]

One way would be to create a file containing a Raw Data tab and Final data:

Please Login or Register  to view this content.

[gaspower]

Good Morning,
Thank you for the reply and I apologize for the late response. I just tried to run the macro on the above file and I get the error "Script Out Of Range"?

Thank you,
JR

[PaulM100]

have you changed the worksheets names to match yours? Upload a sample.

[mikerickson]

You could use a routine like this. You may need to adjust the rngData and rngDestination to suit your situation.
Note the Option Base 1 at the start of the module, outside of any procedure.

Please Login or Register  to view this content.

[gaspower]

Hello Paul,

I have now changed WS1 tab to "Raw Data", and WS2 to "Final Data" in the workbook. I ran the macro, but nothing is copied to WS2. I do not see a way to attach the sample file.


Thank you

[gaspower]

Hello Paul,

File is attached now. Thank you

[Marc L]

Hi !

Better is to attach before & after workbooks …

And I may need only a single codeline just using an advanced filter if destination worksheet headers are same than on source worksheet …

[PaulM100]

The macro assumes that the data should be copied based on the header values. So, in order for it to work, you have to already have the headers wrote in your desired order. Then the data will be copied from a tab to the next one based on that.
Do you want to have the order in the macro and to be copied in the next tab based on that order(headers+data)?

[Marc L]

File is attached now.

No need any VBA code just using Excel basics : an EZ advanced filter !

According to your attachment, once headers filled in destination worksheet (no typo allowed !),
a only one codeline (as more is superfluous !) VBA demonstration as a starter :

Sub Demo1()
    Sheet1.UsedRange.AdvancedFilter xlFilterCopy, , Sheet2.UsedRange.Rows(1)
End Sub 

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[gaspower]

Hello Paul,

thank you. I understand. If I am reading the last sentence correctly, yes, it would be great if header and data could be copied to WS2, without the headers already existing in WS2.

Thank you very much.

[PaulM100]

You will need to provide a order for the headers.