+ Reply to Thread
Results 1 to 23 of 23

Inserting the data in to respective columns

  1. #1
    Forum Contributor
    Join Date
    07-17-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    460

    Inserting the data in to respective columns

    Hi All,

    I have an excel workbook with some common heading column. Now I need to insert all the data into the respective columns in a separate workbook.
    Is there any VB Code or Macro to do so? Please find the attachment.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    07-17-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    460

    Re: Inserting the data in to respective columns

    Once after inserting the data. The new workbook should contain both the common column like

    ID Contact Designation Company Address

  3. #3
    Forum Contributor
    Join Date
    07-17-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    460

    Re: Inserting the data in to respective columns

    Hi All,

    Any solutions for this. In access we have an option to insert all the data into their respective column like Insert query. Same like do we have in vb code?
    Last edited by sathishkm; 09-28-2013 at 06:45 PM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,239

    Re: Inserting the data in to respective columns

    Just an observation...you are not doing yourself any favors by keep posting onto your own thread like that. Many members look for threads with zero posts, and focus on those posts - by posting twice on your own thread, it looks like a discussion is underway.

    I looked at your 2 files, and none of the names, so Im not sure what you want to use to combine them?

    IF you have a common field - like name (although company ID number would be better), you can use this in file 1 to pull in matching data from file 2...
    =VLOOKUP(B2,'[File2(1).xlsx]Sheet1'!A$2:C$9,2,0)

    change ,2, to ,3, etc for successive columns
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Contributor
    Join Date
    07-17-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    460

    Re: Inserting the data in to respective columns

    The above formula is not getting the result.

  6. #6
    Forum Contributor
    Join Date
    09-30-2013
    Location
    Toronto, ON
    MS-Off Ver
    Excel 2007
    Posts
    229

    Re: Inserting the data in to respective columns

    Is the intent to synchronize the two files? So that both would contain the following columns/data
    ID Contact Designation Company Address
    If so, it can be done with VBA code. It may not be easy, but it can be done. One complication would be trying to match on name

  7. #7
    Forum Contributor
    Join Date
    07-17-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    460

    Re: Inserting the data in to respective columns

    Yes I have to synchronize the two file.

  8. #8
    Forum Contributor
    Join Date
    09-30-2013
    Location
    Toronto, ON
    MS-Off Ver
    Excel 2007
    Posts
    229

    Re: Inserting the data in to respective columns

    There are some other questions:
    - do you need to maintain 2 files or can 1 then be dropped?
    - does the column order matter at all?
    - if there is a discrepancy with data between the two files for the same person, which takes precedence?

    Some VBA Code would be required, but it wouldn't be something simple.

  9. #9
    Forum Contributor
    Join Date
    07-17-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    460

    Re: Inserting the data in to respective columns

    1. Single file clubbed with both data.
    2. Column ordering doesn't matter at all.
    3. Whatever the data are there just merge in with the file1 in the respective column.

  10. #10
    Forum Contributor
    Join Date
    07-17-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    460

    Re: Inserting the data in to respective columns

    In access we can do it by INSERT query.

  11. #11
    Forum Contributor
    Join Date
    07-17-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    460

    Re: Inserting the data in to respective columns

    1. Single file clubbed with both data.
    2. Column ordering doesn't matter at all.
    3. Whatever the data are there just merge in with the file1 in the respective column.

  12. #12
    Forum Contributor
    Join Date
    09-30-2013
    Location
    Toronto, ON
    MS-Off Ver
    Excel 2007
    Posts
    229

    Re: Inserting the data in to respective columns

    As far as I know there is no native INSERT function, or anything similar to it, in Excel. You can simulate it somewhat if you want to do a lot of coding.
    If this is a one time thing, it would be easier to do it manually.

  13. #13
    Forum Contributor
    Join Date
    07-17-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    460

    Re: Inserting the data in to respective columns

    I have many files like this to merge in to one file. Every time I will try to do it manually. I am looking for the formula or a VB code to do my task simpler.

  14. #14
    Forum Contributor
    Join Date
    09-30-2013
    Location
    Toronto, ON
    MS-Off Ver
    Excel 2007
    Posts
    229

    Re: Inserting the data in to respective columns

    Creating the code to this would take some time and would likely need some discussion to nail down the process.
    If you have the experience with MS Access, why not use the Excel files as an external data source and then use the MS Access INSERT queries to build the database. If I remember correctly, you can then use the MS Access database as a source for Excel.

  15. #15
    Forum Contributor
    Join Date
    07-17-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    460

    Re: Inserting the data in to respective columns

    I have a vb code to merge the data according to the sheet. But it will not merge according to the column. Can you modify this code?

    Sub GetSheets()
    Path = "C:\Users\sathish.STECHBLR\Desktop\"
    Filename = Dir(Path & "*.xlsx")
    Do While Filename <> ""
    Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
    For Each Sheet In ActiveWorkbook.Sheets
    Sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next Sheet
    Workbooks(Filename).Close
    Filename = Dir()
    Loop


    Dim J As Integer
    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add
    Sheets(1).Name = "Combined"

    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")

    For J = 2 To Sheets.Count
    Sheets(J).Activate
    Range("A1").Select
    Selection.CurrentRegion.Select

    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

    Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next


    End Sub

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,239

    Re: Inserting the data in to respective columns

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

  17. #17
    Forum Contributor
    Join Date
    09-30-2013
    Location
    Toronto, ON
    MS-Off Ver
    Excel 2007
    Posts
    229

    Re: Inserting the data in to respective columns

    I was able to get this done faster that I thought.
    This code should do the trick. (I couldn't figure out how to add a file to a reply)
    It worked when tested with your files but I can't guarantee that it will work with all the files you may have to combine. You may want to add some code to change formatting afterwards.
    You'll have to adjust the path (sPath variable) in the code
    If this is want you want, please click on the Add Reputation link at the bottom.
    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    07-17-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    460

    Re: Inserting the data in to respective columns

    The code is not executing.

  19. #19
    Forum Contributor
    Join Date
    09-30-2013
    Location
    Toronto, ON
    MS-Off Ver
    Excel 2007
    Posts
    229

    Re: Inserting the data in to respective columns

    what is it not doing? You'll need to manually start it, unless you set up a button to start it off.
    Did you change the value of sPath when you copied it into your file?
    One other thing you may need to change. I was looking for .xlsx files. If you want .xls, or something else, you need to change this line
    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    07-17-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    460

    Re: Inserting the data in to respective columns

    I am getting the error message say that " Cant execute code in break mode" in

    Do While Len(Trim(Worksheets(sh_Src).Cells(nRow, nCol).Value)) > 0

  21. #21
    Forum Contributor
    Join Date
    09-30-2013
    Location
    Toronto, ON
    MS-Off Ver
    Excel 2007
    Posts
    229

    Re: Inserting the data in to respective columns

    It sounds like you're trying to execute a macro when one is already in break mode.
    See this article http://support.microsoft.com/kb/177828

    I have no issues running the code opening the file and running the macro.

  22. #22
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,239

    Re: Inserting the data in to respective columns

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

  23. #23
    Forum Contributor
    Join Date
    07-17-2012
    Location
    bangalore
    MS-Off Ver
    Excel 2007
    Posts
    460

    Re: Inserting the data in to respective columns

    Any solution about this query?

+ 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: 4
    Last Post: 12-13-2011, 03:17 PM
  2. Searching Columns and Copying the respective Rows
    By Erenagh in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-10-2011, 11:52 AM
  3. Compare column A between 2 worksheets and sum respective columns
    By brahmathereturn in forum Excel General
    Replies: 6
    Last Post: 02-15-2011, 09:56 PM
  4. Default Track all the changes of all the columns in respective rows of column A
    By aravindhan_31 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-25-2010, 06:29 AM
  5. Comparing Columns and getting a respective value.
    By Sake9t in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-26-2006, 10:27 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