Help using Named Range in VBA tp pupultate a worksheet.

[mykal66]

Hi everyone - I'm looking for help using named ranges in VBA to populate data in a worksheet if anyone can help please?

I've attached an example of what i am trying to get to work.

Sheet3 literally takes the names of the separate teams on sheet2 to make a named range 'TEAMS'

Sheet2 will contain the separate team info lists and each will have a named range to match their team name e.g. Team1 range A3:B5 is named Team1

On Sheet1 users would use the button and pop up box to select which team information they want to see. The name of the team they have selected shows in cell G4 on sheet 1. This is where i get stuck; i need the code to use the name in G4 and then display the same named range underneaths starting in G5.

As always, help or pointers is appreciated.

Mykal

Example.xlsm

[Kenneth Hobs]

Private Sub CommandButton1_Click()  Dim r As Range
  With Worksheets("Sheet1")
    Set r = Range(CBO1.Value)
    .Range("G4").Value = CBO1.Value
    .Range("G5").Resize(r.Rows.Count, r.Columns.Count).Value = Range(CBO1.Value).Value
  End With
  Unload Me
End Sub


Private Sub UserForm_Initialize()
  Dim cpro As Range
  Dim cAdd As Range
  Dim cFin As Range
  Dim ws As Worksheet
  Set ws = Worksheets("Sheet3")
  ' set the range of cells to to populate drop down list and process name attached to that range
  For Each cpro In ws.Range("Teams")
    With Me.CBO1
      .AddItem cpro.Value
      .List(.ListCount - 1, 1) = cpro.Offset(0, 1).Value
    End With
  Next cpro
  CBO1.ListIndex = 0
End Sub

[mykal66]

Hi Kenneth

Thank you very much - really appreciated and works perfect

Mykal

[mykal66]

[QUOTE=Kenneth Hobs;316906][CODE]Private Sub CommandButton1_Click() Dim r As Range
With Worksheets("Sheet1")
Set r = Range(CBO1.Value)
.Range("G4").Value = CBO1.Value
.Range("G5").Resize(r.Rows.Count, r.Columns.Count).Value = Range(CBO1.Value).Value
End With
Unload Me
End Sub

Hi again Kenneth

I've had another thought and wondered if you are able to help please? Instead of using populating the worksheet is it possible to populate a label on a user form with the range data? I would use the initial form on the example I attached but then add a label e.g. lbl1.

I have been on various forums and tried to adapt the code you gave me e.g. still populate a cell with the selection, use that as the name of the range I want and then tried get that range data into the label but unable to get it to work.

Private Sub CommandButton1_Click()
Dim r As Range
    With Worksheets("Sheet1")
        Set r = Range(CBO1.Value)
        .Range("G4Value = CBO1.Value
        Directroy.lbl1.Caption = Range(CBO1.Value).Value
    End With
    Unload Me
End Sub

Thank you again for your help with original query

Best wishes