Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

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

  1. #1
    Join Date
    12th July 2004
    Posts
    153

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    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.

  3. #3
    Join Date
    12th July 2004
    Posts
    153
    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    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.

    VB:
    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.

  5. #5
    Join Date
    12th July 2004
    Posts
    153
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th July 2004
    Posts
    153
    Tom, my bad. Sorry got it now.

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Data Field Of Pivot Table To Go Across Columns
    By dannZ in forum EXCEL HELP
    Replies: 7
    Last Post: October 17th, 2007, 17:05
  2. Pivot Table Data In Multiple Columns
    By rpaulson in forum EXCEL HELP
    Replies: 4
    Last Post: June 1st, 2007, 06:32
  3. Compile List of Specific Data From Multiple Columns
    By slick225 in forum EXCEL HELP
    Replies: 4
    Last Post: August 31st, 2006, 14:02
  4. Replies: 3
    Last Post: October 29th, 2005, 06:07
  5. Replies: 1
    Last Post: June 8th, 2005, 21:57

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
  •  
porno