+ Reply to Thread
Results 1 to 7 of 7

How to align column in first sheet and then copy to second sheet using VBA

  1. #1
    Registered User
    Join Date
    11-03-2017
    Location
    US
    MS-Off Ver
    7
    Posts
    23

    How to align column in first sheet and then copy to second sheet using VBA

    Hi,

    In the attached sheet, I want the exact output available in sheet "Myoutput" to be copied from sheet1.

    In sheet1, the data are not aligned for particular column. Therefore the macro should be designed such that wherever it finds the column(e.g. to find country from column named as /team/manager/user/userid/countryid/countryname), it should populate the same text value in "Myoutput" sheet.

    I am not able to find solution how to proceed if data are present in zig zag manner in diff column.
    Attached Files Attached Files

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: How to align column in first sheet and then copy to second sheet using VBA

    Try this - it gets one more line than your sample:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Registered User
    Join Date
    11-03-2017
    Location
    US
    MS-Off Ver
    7
    Posts
    23

    Re: How to align column in first sheet and then copy to second sheet using VBA

    Thank you very much for the solution.
    I need your expert suggestion. Actually there are around 5000 XML files in a folder, I am importing those into the excel file using below macro and then using your design macro to populate my expected data.

    I would like to know is there any possibility to capture 4-5 columns directly from the individual XML file by looping to all 5000 XML file so that I will get the output in a more faster way.

    I want to capture the below four columns available in respective path. I want to use directly the path name of column because of duplicate column available in xml file

    Please Login or Register  to view this content.
    I use below macro code to import all xml file data into excel. How can I fit above four columns within to restrict data only to four columns.
    Please Login or Register  to view this content.
    Thanks in advance
    Last edited by tikistat; 11-03-2017 at 07:25 PM.

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: How to align column in first sheet and then copy to second sheet using VBA

    You're welcome

    You're probably better off loading them all into the xswb book and then just running the routine, rather than have the routine on each separate extraction. But, if they're like the sample you could - maybe inject the code between these lines (note the apostrophe):

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-03-2017
    Location
    US
    MS-Off Ver
    7
    Posts
    23

    Re: How to align column in first sheet and then copy to second sheet using VBA

    Thanks once again for your help.

    I am getting run time error 91 (object variable or with block variable not set) for column E when the length of column name increases.

    I think column name can be stored in a variable and then could be used. I am newbie and may be wrong.

    Attaching sheet for reference.
    Attached Files Attached Files

  6. #6
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: How to align column in first sheet and then copy to second sheet using VBA

    This works:

    Please Login or Register  to view this content.

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: How to align column in first sheet and then copy to second sheet using VBA

    You're welcome and thanks for the rep!

+ 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. Replies: 1
    Last Post: 02-09-2017, 05:36 AM
  2. Replies: 1
    Last Post: 02-09-2017, 05:30 AM
  3. Replies: 9
    Last Post: 06-27-2016, 01:02 AM
  4. [SOLVED] Macro to copy data from multiple sheets to overview and align results on overview sheet
    By McBree in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-04-2015, 04:01 PM
  5. Replies: 2
    Last Post: 01-22-2014, 05:36 PM
  6. Copy column data on one sheet to row or column date on another sheet based on user in
    By soready42012 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-08-2012, 12:03 AM
  7. Replies: 1
    Last Post: 10-30-2009, 10:58 AM

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