auto fill cells or auto populate cells

Microsoft Microsoft excel 2007 (pc)
August 17, 2010 at 10:39:34
Specs: Windows XP
Okay not sure if Excel can do this but I thought I'd ask....

I want to create a workbook that has a "veryhidden" sheet with Sales Person, Stores, Sales $. I want to create a sheet that will reference this data. What I would like it to do is if I enter a sales persons name in a cell on another sheet that a range of rows will list all their stores automatically on that sheet. I'm learning how to create VB macros so I'm sure this might be something I need to do there. Also, Various Sales persons can have from 10 to 37. stores.

See More: auto fill cells or auto populate cells

Report •

August 17, 2010 at 12:34:39

If you salesperson names are in cells on row 1, starting in column A on a worksheet named "LinkedNames"
and underneath each name in the same column is a list of 'their' stores - any number of stores

For example A1 contains "Joe" and A2 to A20 contain store names for Joe.

Then entering Joe in Sheet1, cell A1 will create the list of his stores on Sheet1, in cells A2 to A20

On Sheet1, right-click the name tab and select 'View Code'

Enter this in the main VBA window:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngLinkStart As Range
Dim rngLinkEnd As Range
Dim rngShopEnd As Range
Dim rngCell As Range
Dim rngCell2 As Range
Dim blnFound As Boolean
Dim intShopRow As Integer
Dim n As Integer

On Error GoTo ErrHnd:

'disable events, so that changes made by this code
'do not re-trigger it
Application.EnableEvents = False

'only respond to changes in cell A1
If Target.Address = "$A$1" Then

    'clear shop names below salesperson's name
    'code assumes max 200 stores per person
    For n = 1 To 200
        'test cells below changed name
        'exit when we get to an empty one
        If Target.Offset(n, 0) = "" Then
            Exit For
            'clear cell contents
            Target.Offset(n, 0).ClearContents
        End If
    Next n
    'set start cell of list of salesperson's names
    Set rngLinkStart = Worksheets("LinkedNames").Range("A1")
    'find last salesperson's name in row
    Set rngLinkEnd = Worksheets("LinkedNames").Range("A1") _
            .Offset(0, CStr(Application.Columns.Count - 1)) _
    'set salesperson name found flag to false
    blnFound = False
    'set shop name row counter to 1
    intShopRow = 1
    'loop through sales person names to find name
    For Each rngCell In Worksheets("LinkedNames") _
            .Range(rngLinkStart.Address, rngLinkEnd.Address)
        If rngCell.Text = Target.Text Then
            'matching name found
            blnFound = True
            'now find last shop in this salesperson's column
            Set rngShopEnd = Worksheets("LinkedNames") _
                    .Cells(Application.Rows.Count - rngCell.Row, rngCell.Column) _
            'loop through all shop names for this salesperson
            For Each rngCell2 In Worksheets("LinkedNames") _
                    .Range(rngCell.Offset(1, 0).Address, rngShopEnd.Address)
                'move each shop name into cells beneath name
                Target.Offset(intShopRow, 0).Value = rngCell2.Text
                'increment shop name row
                intShopRow = intShopRow + 1
            Next rngCell2
        End If
    Next rngCell
End If

're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
're-enable events
Application.EnableEvents = True
End Sub

This macro uses the change event - whenever a value changes in a cell on Sheet1, this code is run.
If the changed cell is not cell A1, then the code finishes (exits sub)
If it is A1 (and this can be changed to a different cell or even a range of cells), the name entered is looked up on the LinkedNames worksheet and the associated shops are listed (any previous list is erased first).

Test this code using a new workbook with two worksheets Sheet1 and LinkedNames
With data on LinkedNames as described and entering a name in cell A1 on Sheet1.

Once you have it working, it can be changed to match your exact requirements.


Report •
Related Solutions

Ask Question