Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Import row to first available row (2002+)

    Is it possible (probably VB) to insert the contents of one specific row and a fixed set of columns from one Excel sheet into another workbook and sheet, and into the first unused row?

    I have a series of workbooks being sent by various people and need a given row (e.g., row 8, cols A-F) of info from each to be inserted into the next (over and over again) available row of the master workbook/worksheet into the respective columns (A-F, as an example).

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import row to first available row (2002+)

    Say that column A is always populated in the rows to be copied. You could use code like this:

    Sub Transfer()
    Dim wshTarget As Worksheet
    Dim lngNextRow As Long
    Set wshTarget = Workbooks("Master.xls").Worksheets("Target")
    lngNextRow = wshTarget.Range("A65536").End(xlUp).Row + 1
    ActiveSheet.Range("A8:F8").Copy wshTarget.Range("A" & lngNextRow)
    End Sub

    Substitute the correct name of the master workbook and worksheet.
    When this code is run, both the Master workbook and the source workbook must be open, and the source worksheet must be the active worksheet.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Import row to first available row (2002+)

    Thanks for the quick (as usual) response, Hans. I'm trying to understand what this is doing or supposed to do.
    I can't seem to get it to do what I tried to describe. Surely, my description or something related.

    If I want to move the data from row 8, cols A-F from the active workbook to the Master workbook and the Target sheet to the first available non-blank row in the same cols (A-F)...this didn't seem to do it.

    I placed the Transer() macro in the source/active workbook (not the Master) and ran it. Or, is that backwards?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import row to first available row (2002+)

    It is not really important where you put the macro, but I would put it in the Master workbook, since that is fixed.
    The workbook from which you want to import should be the active (foreground) workbook when you run the macro.

    The macro uses column A in the target worksheet to determine the next row to copy to.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Import row to first available row (2002+)

    OK. Got it and it's now doing just what I need. Thanks again.

Posting Permissions

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