Announcement

Collapse
No announcement yet.

How to insert columns of data into specific columns in another table

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to insert columns of data into specific columns in another table



    Hi. I have some data in a table whose format doesn't match the column formats in another worksheet.

    I need to insert the data in this table (beginning at row 3 and ending at row 10) into the other worksheet. I want to append rows in the 2nd worksheet with this new data. I do not want to overwrite. (Thus I need to identify what's the last row in the 2nd worksheet too.)

    But at the same time, I need to correctly place the available data in the right columns. I want to place filler data and/or blanks in the other columns.
    ex.

    source:

    row2: sex name: score:
    row3: m john 5
    row4: m mike 10
    row5: f sarah 10
    ...10: f paula 9

    target:

    row1: sex name school: town: score:
    row11: m john hopkins new york 5
    row12: m mike hopkins new york 10
    row13: f sarah hopkins new york 10
    ... 20: f paula hopkins new york 9

    How do I do this?

    Please insert comments in big chunks of code so I can understand what they are for.

    Thanks in advance.

  • #2
    It looks like this may have been answered as part of your later post (the following thread).

    http://www.ozgrid.com/forum/showthread.php?t=21417

    Please post back if you still need more info.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

    Comment


    • #3
      Thanks Tom.

      Those subs were really right on the money.

      1. How do I create a command button to call those subs?

      2. Suppose Test2.xls were closed. If the code needs the workbook to be open, how do I invoke it to open when it is in the unopened state?

      3. I have another input data file with similar fields, but some are not. Some are different and need to be classified under a different heading or column. Before creating the table for thie input data file, I am thinking of supplying null or zero value for the fields where it is not similar. How do I do this? Below is an illustration of what I am thinking:

      Field 1 - similar
      Field 2 - not similar
      Field 3 - similar
      Field 4 - new

      Program should enter data into file as:

      Column 1 Column 2 Column 3 Column 4
      Tom 0 100 USA

      Comment


      • #4
        Question 2 first:

        The following will open the file first if it is not already open (this assumes the file is in the same folder as the open file you are calling it from.

        Code:
        Sub MoveTable()
        Dim LastRow As Integer, MyArray As Variant, NewLastRow
        LastRow = Sheets("Master").Range("A65536").End(xlUp).Row
        MyArray = Worksheets("Master").Range("A1:L" & LastRow)
        On Error Resume Next
            Workbooks.Open FileName:= _
                ThisWorkbook.Path & "\" & "Test2.xls"
            Windows("Test2.xls").Activate
        On Error GoTo 0
        NewLastRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
        Worksheets("Sheet1").Range("A" & NewLastRow + 1 & ":L" & NewLastRow + LastRow) = MyArray
        End Sub
        If the file is in a different location, then you need to provide the full path name: THat is, in stead of

        Workbooks.Open FileName:= _
        ThisWorkbook.Path & "\" & "Test2.xls"

        You will need something like (modified to match your actual file path location and filename:

        Workbooks.Open FileName:= _
        "C:\Data Files\EXCEL Stuff\OzGrid\Test\Test2.xls"

        For question #1:

        I usually insert a text box from the drawing tool. You can format the text box howeer you like, and then right click it and select "Assign Macro" from the pop-up dialog box, then select teh macro you want linked to it from the list of available macros and click"ok." Note that to edit the text box after this you will need to right click it and select "Edit." A left (normal) click will run the macro rather than select teh text box.
        Best Regards,
        Tom
        ---------------------------
        Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

        Comment


        • #5
          Tom, what does this chunk of code do?

          On Error Goto 0
          NewLastRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
          Worksheets("Sheet1").Range("A" & NewLastRow + 1 & ":L" & NewLastRow + LastRow) = MyArray

          Thanks.

          Comment


          • #6


            Tom, my bad. Sorry got it now.

            but where does "on error goto 0" make the program go?

            Comment

            Working...
            X