+ Reply to Thread
Results 1 to 12 of 12

Macro To Organize Header Columns

  1. #1
    Forum Contributor
    Join Date
    07-15-2007
    Location
    Bend, Oregon
    MS-Off Ver
    Office 2016 Windows
    Posts
    161

    Macro To Organize Header Columns

    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
    Last edited by gaspower; 05-22-2018 at 08:52 AM.

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Macro To Organize Header Columns

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

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    07-15-2007
    Location
    Bend, Oregon
    MS-Off Ver
    Office 2016 Windows
    Posts
    161

    Re: Macro To Organize Header Columns

    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

  4. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Macro To Organize Header Columns

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

  5. #5
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Macro To Organize Header Columns

    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.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  6. #6
    Forum Contributor
    Join Date
    07-15-2007
    Location
    Bend, Oregon
    MS-Off Ver
    Office 2016 Windows
    Posts
    161

    Re: Macro To Organize Header Columns

    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
    Attached Files Attached Files
    Last edited by gaspower; 05-23-2018 at 10:37 AM.

  7. #7
    Forum Contributor
    Join Date
    07-15-2007
    Location
    Bend, Oregon
    MS-Off Ver
    Office 2016 Windows
    Posts
    161

    Re: Macro To Organize Header Columns

    Hello Paul,

    File is attached now. Thank you

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Macro To Organize Header Columns


    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 …

  9. #9
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Macro To Organize Header Columns

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

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Macro To Organize Header Columns

    Quote Originally Posted by gaspower View Post
    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 :

    PHP Code: 
    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 » !

  11. #11
    Forum Contributor
    Join Date
    07-15-2007
    Location
    Bend, Oregon
    MS-Off Ver
    Office 2016 Windows
    Posts
    161

    Re: Macro To Organize Header Columns

    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.

  12. #12
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Macro To Organize Header Columns

    You will need to provide a order for the headers.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to delete columns based on header
    By sfadam in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-25-2015, 05:15 PM
  2. [SOLVED] Organize multiple columns into organised Labelled columns
    By ivanpersie in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-30-2015, 10:36 AM
  3. organize columns in a better format
    By travelmail26 in forum Excel General
    Replies: 10
    Last Post: 12-05-2014, 01:17 PM
  4. Macro to unlock cells in particular columns based on header value
    By Napa in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-17-2013, 05:20 PM
  5. macro to delete blank columns - includes header row
    By bluesea in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-31-2012, 12:18 PM
  6. Organize Rows and Columns
    By Hady in forum Excel General
    Replies: 1
    Last Post: 08-09-2005, 07:05 AM
  7. Organize and sort columns
    By J in forum Excel General
    Replies: 2
    Last Post: 02-28-2005, 09:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1