Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Dec 2002
    Thanked 0 Times in 0 Posts

    Auto workbook update (2000)

    Is there a way to have just a part of a workbook updated to another workbook?
    We have a list of over 7000 names in a excel workbook. I would like to create another workbook that will update or add the names into it only if in column G I have the words (claimed prize). My boss is only interested in the names that have claimed their prizes; he does not want all the other names. One day I might add only 5 names to the list, another day I might add 20 names to the list. Right now every day I have to do a sort then a copy and a paste into a shared workbook for him.
    Thanks for any help
    Thanks for any help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 29 Times in 29 Posts

    Re: Auto workbook update (2000)

    You can use Advanced Filter for this. In the same worksheet that contains the list of names, create a criteria range, consisting of the column header (field name) of column G in one cell, and the text (claimed prize) in the cell below it.
    The target workbook should have the column headers (field names) of the columns you want to extract. Select these column headers, then select Data | Filter | Advanced Filter. You'll probably get an error message, just click OK. Then specify the List Range and the Criteria Range (in the other workbook). Tick the Copy to another location check box, then specify the Copy to range in the target workbook. Click OK.

    This can be automated in a macro to be run from the source workbook. Modify the values marked with ***.

    Sub Extract()
    Dim wbkSource As Workbook
    Dim wbkTarget As Workbook
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim rngDatabase As Range
    Dim rngCriteria As Range
    Dim rngTarget As Range

    Set wbkSource = ThisWorkbook
    Set wshSource = wbkSource.Worksheets("Sheet1") ' ***
    Set rngDatabase = wshSource.Range("A1").CurrentRegion ' ***
    Set rngCriteria = wshSource.Range("Criteria") ' ***

    Set wbkTarget = Workbooks.Open("Export.xls") ' ***
    Set wshTarget = wbkTarget.Worksheets("Sheet1") ' ***
    Set rngTarget = wshTarget.Range("Extract") ' ***

    rngDatabase.AdvancedFilter xlFilterCopy, rngCriteria, rngTarget

    Set rngTarget = Nothing
    Set wshTarget = Nothing
    Set wbkTarget = Nothing
    Set rngCriteria = Nothing
    Set rngDatabase = Nothing
    Set wshSource = Nothing
    Set wbkSource = Nothing
    End Sub

    Note: the code expects that you have given the criteria range the name Criteria (in Insert | Name | Define...), and that you have created a workbook named Export.xls, with the column headers in a range named Extract. These names can be changed according to your preferences.

Posting Permissions

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