Hello all, I would like to point out that I am a novice when it comes to VBA coding. I am working on a project and currently need some assistance. I am going in iterations so I will be focusing on my current problem. Problem - I have a userform with a listbox with a list of teams and a button that should be transferring the selected teams on the listbox and returning all the associated row values e.g. Team's city, state, profit, members, etc onto a different worksheet. This is what I currently have:
Code
Private Sub CommandButton1_Click()
Dim datasheet As Worksheet 'where data is copied from
Dim reportsheet As Worksheet 'where data will be pasted
Dim TeamAs String
Dim finalrow As Integer
Dim i As Integer 'row counter
Set datasheet = Sheet1
Set reportsheet = Sheet2
Team = reportsheet.Range("A2:A45").Value 'I am having difficulty here
'clear old data from report / %Peak Sheet
reportsheet.Range("B2:D100").ClearContents
'go to datasheet and start searching and copying row data
datasheet.Select
finalrow = Cells(Rows.Count, "A").End(xlUp).Row
'loop starting from row 2 to final row to find matching records
For i = 2 To finalrow
If Cells(i, 1) = Team Then
Range(Cells(i, 1), Cells(i, 4)).Copy 'copy all data from column 1 to 4
reportsheet.Select 'go to the report sheet
Range("A200").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
datasheet.Select
End If
Next i
reportsheet.Select 'purpose for this code so when procedure is over, this sheet is selected
Range("A2").Select
End Sub
-----
'Code for pulling in the team list from the worksheet "ModifiedSupply"
Private Sub UserForm_Initialize()
Dim TeamList As Variant
Sheets("ModifiedSupply").Activate
ListBox1.RowSource = Range("A2:A45").Address
End SubGreatly appreciated,
Andrew