Trying to create a spreadsheet that combines VLOOKUPs based on inputbox

[ExcelDropper]

Hi.

I'm struggling with a macro and was hoping someone could help please.

In the attached workbook I have 5 worksheets. The 'data' worksheet is a list of teams and their respective hard-input values based on a session and category. The 'main' worksheet will do a vlookup for the team inputted in A1, looking to bring back the correct values based on session and category. Before I run my macro (found in thisworkbook), these are the only two worksheets available. After you run the macro, it will create a 'teams' and 'teams list' worksheet. The macro is as follows:

Sub Teams()
Dim TeamsCount As Long
Dim TeamCode As Variant
Dim i As Long

On Error Resume Next
Sheets("Teams").Delete
Sheets("Teams List").Delete
On Error GoTo 0

Sheets.Add.Name = "Teams"
Sheets.Add.Name = "Teams List"
Sheets("Main").Range("b2:f7").Copy
Sheets("Teams").Range("b2:f7").PasteSpecial xlFormulas

TeamsCount = InputBox("How many teams are you comparing?")
Sheets("Teams List").Activate
For i = 1 To TeamsCount
TeamCode = InputBox("Enter team code: " & i)
Sheets("Teams List").Range("a1").Offset(i - 1, 0).Value = TeamCode
Next i

End Sub

What this does is replicates the data from 'main' to 'teams' without A1. Then, it asks you how many teams you wish to compare and what codes. These codes will be stored in teams list. However, I want to adjust this macro as currently it comes up with #N/As. The reason for this is because its trying to do a VLOOKUP without any value in A1. What I want to happen, as shown by the red sheet, is for each value to do a vlookup to the values in 'teams list'. If you, for the first inputbox, said '2' teams, and for the second inputbox said 'team a' and 'team b', you would get what I have in sheet 'teams list'. Now I just want my 'teams' sheet to replicate the red sheet. Hopefully this makes sense and someone can help me with this please? I've tried myself but as the number of teams are changing every time, the number of vlookups per cell need to change as well, but I'm simply not certain on how to do this.

Thanks for reading.

Example.xlsm

Shared with Dropbox

www.dropbox.com

 

[Zot]

What about the Main sheet? You have Session 1, 2 and 3 but the Category list can be any number?