Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    fill child table from master (excel 2000)

    I have two tables. One is a master table which is filled in and the other is a child which has the option of being filled in by the Master or done manually. This is what I am tring to achieve.
    There will be a button which is click to fill in the the child table from the columns specified.

    The Range B10:B64 from Master! will be copied and pasted into B10:B64 child!
    The Range E10:E64 master! will be copied and inserted into E10:E64 child!
    F10:F64 master to F10:F64 child
    G10:G64 mater to G10:G64 child

    This action will happen every time the button is clicked

    heres my non working code:



    Sub FillInChild()
    Sheets("Master").Select
    Range("B10:B64").Copy Sheets("Child").Range("B10").Paste
    Sheets("Master").Select
    Range("E10:E64").Copy Sheets("Child").Range("E10").Paste
    Sheets("Master").Select
    Range("F10:F64").Copy Sheets("Child").Range("F10").Paste
    Sheets("Master").Select
    Range("G10:G64").Copy Sheets("Child").Range("G10").Paste
    Application.CutCopyMode = False
    MsgBox = "child is filled"

    End Sub

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill child table from master (excel 2000)

    How about this:

    <code>
    Sub FillInChild()
    Sheets("Master").Range("B10:B64").Copy Destination:=Sheets("Child").Range("B10")
    Sheets("Master").Range("E10:E64").Copy Destination:=Sheets("Child").Range("E10")
    Sheets("Master").Range("F10:F64").Copy Destination:=Sheets("Child").Range("F10")
    Sheets("Master").Range("G10:G64").Copy Destination:=Sheets("Child").Range("G10")
    Application.CutCopyMode = False
    MsgBox "child is filled"
    End Sub
    </code>
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill child table from master (excel 2000)

    Thank you,

  4. #4
    New Lounger
    Join Date
    Sep 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill child table from master (excel 2000)

    <pre>Sub foo()
    Application.ScreenUpdating = False
    On Error Resume Next
    With Worksheets("Master")
    .Range("B10:B64").Copy Destination:=Sheets("Child").Range("B10")
    .Range("E10:G64").Copy Destination:=Sheets("Child").Range("E10")
    End With
    Application.ScreenUpdating = True
    MsgBox "Child is filled"
    End Sub

    </pre>


  5. #5
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill child table from master (excel 2000)

    What would you change in this in order to have it work across a number of workbooks? For example a number of workbooks that would automatically be copied up to a Master or Summary workbook? I imagine that it would have to have something like Workbook1, sheet such and such, range such and such copied to Workbook 2, sheet such and such, range such and such, if it is possible.

    Thanks.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill child table from master (excel 2000)

    You would have to give us more information:

    1- Are all of the workbooks open, or would the VBA code have to open each workbook?

    2- How would the worbooks be identified? Do they have something unique about their names, like do the names all start with the same characters. In other words, if the workbooks are already open, how would the code know which of the open workbooks to copy from? If the workbooks are to be opened by the code, where are they located and how would the code know which workbooks to open?

    3- Where is the data to be copied from? Is the sheet name always the same in every workbook? Are the cell ranges to be copied the same in each workbook?

    4- Where is the data to be copied to? After the first workbook is copied, it does not make sense to copy the data from subsequent workbooks to the same range. Does the data get copied to the same range on different sheets? To different ranges on one sheet?
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill child table from master (excel 2000)

    The workbooks would be open. Lets call one Alpha, the other Bravo. My intention would be to have the Master sheet from Alpha copy the information over to a Child sheet on the Bravo workbook. In the code you provided it is the information that is copied from your first line of code that would be copied to the Child sheet in the Bravo workbook. If this line can be used to do it , other lines could be changed to go to other open workbooks with various sheet names.

    If it can't be done without a lot of coding, I will use the code you provided in a multisheet workbook. I don't have a real life application at present, I am really just curious at to whether or not it can be done across workbooks that are opened.

    Thanks.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill child table from master (excel 2000)

    Does this do what you want?

    <code>
    Workbooks("Alpha.xls").Sheets("Master").Range("B10 :B64").Copy _
    Destination:=Workbooks("Bravo.xls").Sheets("Child" ).Range("B10")
    </code>
    Legare Coleman

  9. #9
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill child table from master (excel 2000)

    Thanks. I see from your code why my first attempt didn't work. Need "Workbooks" not "Workbook". Haven't had a chance to try but I think this will solve what I am trying to accomplish.

Posting Permissions

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