vlookup with a worksheet name? or some other solution?

[slam]

I have a list of countries in a column on a master worksheet, then I have worksheet names that match the countries in that column. I need to extract information on each of the country worksheets and have it populated on the master worksheet.

Here’s a little more info:

A2 to A20 on the master worksheet are the list of countries
B1 to Q1 are numbers 1 through 16

On each Country worksheet Cells A2 through A17 are the same numbers (1 through 16)
Cells B2 through B17 on the 19 country worksheets are what I wanted populated in B2 through Q20 on the Master worksheet

How would I go about this? I have MoreFunc if that helps….?

 

[jmthompson]

Try this VBA code, but alter the range in "For Each c In Sheets("Master").Range("A2:A16")" to the actual range of cells with countries. Also revise the name of your Master sheet to match your name

Sub Populate()
Dim c As Range
Dim ws As Worksheet
For Each c In Sheets("Master").Range("A2:A16")
    For Each ws In Sheets
        If ws.Name = c.Value Then
        Sheets("Master").Range(c.Offset(, 1), c.Offset(, 16)).FormulaR1C1 = "=Vlookup(R1C," & ws.Name & "!R2C1:R17C2,2,False)"
        End If
    Next ws
Next c

End Sub

 

[slam]

Excellent. Looks like it's working for the most part. Needs a little tweaking to account for blanks on the individual country worksheets, but I think I can handle that.

Only thing it is having trouble with is countries with a space in their name (Great Britain, Abu Dhabi, etc)

Thanks

EDIT: I could probably do with some help to handle the blanks actually!

 

[jmthompson]

Can you replace the space with an underscore?

 

[slam]

Can you replace the space with an underscore?

I hate underscores with a passion, but Great Britain just became Britain, and Abu Dhabi became AbuDhabi - that works

 

[jmthompson]

Or, you can keep the country names as they were, eliminate the spaces in the tab names and use this:

Sub Populate()
Dim c As Range
Dim ws As Worksheet
Dim nm As String
For Each c In Sheets("Master").Range("A2:A16")
nm = WorksheetFunction.Substitute(c.Value, " ", "")
    For Each ws In Sheets
        If ws.Name = nm Then
        Sheets("Master").Range(c.Offset(, 1), c.Offset(, 16)).FormulaR1C1 = "=Vlookup(R1C," & ws.Name & "!R2C1:R17C2,2,False)"
        End If
    Next ws
Next c
End Sub

 

[slam]

Or, you can keep the country names as they were, eliminate the spaces in the tab names and use this:

Sub Populate()
Dim c As Range
Dim ws As Worksheet
Dim nm As String
For Each c In Sheets("Master").Range("A2:A16")
nm = WorksheetFunction.Substitute(c.Value, " ", "")
    For Each ws In Sheets
        If ws.Name = nm Then
        Sheets("Master").Range(c.Offset(, 1), c.Offset(, 16)).FormulaR1C1 = "=Vlookup(R1C," & ws.Name & "!R2C1:R17C2,2,False)"
        End If
    Next ws
Next c
End Sub

Much preferred. Thanks

So I am trying to apply that code to do a similar thing on another worksheet, but this time the countries are listed in columns rather than rows. I'm having the hardest time figuring out how this line translates into the formula:

Sheets("Master").Range(c.Offset(, 1), c.Offset(, 16)).FormulaR1C1 = "=Vlookup(R1C," & ws.Name & "!R2C1:R17C2,2,False)

What does C.Offset do?

The row/column references don't seem to translate to what is populated in the cells.....

I'd love to learn this myself!

 

[jmthompson]

Sub Populate() 
Dim c As Range 
Dim ws As Worksheet 
Dim nm As String 
For Each c In Sheets("Master").Range("A2:A16")
'The above code tells the macro to perform all of the following actions for each country code listed on your master sheet in range A2 through A16
nm = WorksheetFunction.Substitute(c.Value, " ", "")
'The above code defines the variable nm as the country code without any spaces
 For Each ws In Sheets
'the above code tells the macro to perform all of the following code on each worksheet in the workbook 
If ws.Name = nm Then
'The above code tests to see if the name of the worksheet matches the variable nm, if it does perform the next action
 Sheets("Master").Range(c.Offset(, 1), c.Offset(, 16)).FormulaR1C1 = "=Vlookup(R1C," & ws.Name & "!R2C1:R17C2,2,False)"
'the above code populates the master sheet's cells in columns B through Q for the current country code with a formula that looks up the header on that country's sheet and returns the value 1 cell to the right  
End If     
Next ws
'The above code moves to the next worksheet
 Next c
'After the code loops through all of the sheets, the above code directs the macro to move to the next country code on the master sheet and repeat the previous steps 
End Sub

 

[jmthompson]

What is the layout of the new workbook, is everything transposed?
On the master, countries across the columns and #'s down the rows and then on the country sheets, #'s across the columns and values beneath?

 

[slam]

What is the layout of the new workbook, is everything transposed?
On the master, countries across the columns and #'s down the rows and then on the country sheets, #'s across the columns and values beneath?

It is transposed indeed, but it is serving a different purpose so it is using a different lookup value.

Thanks for explaining that whole thing.

the above code populates the master sheet's cells in columns B through Q for the current country code with a formula that looks up the header on that country's sheet and returns the value 1 cell to the right

This much I knew, but I'm trying to make sense of what the values are referring to

Like this part:
c.Offset(, 1), c.Offset(, 16))

And this part:
FormulaR1C1
(nothing takes place in row 1 column 1?)

And this part:
R1C
(why isn't there a column #?, and shouldn't it be row 2?)

I get that this part refers to the range and the 2nd column of the rage from where values should be pulled:
R2C1:R17C2,2

The rest I'm having trouble piecing together. The formula its self makes perfect sense when in the cell, but I'm having trouble translating. Once I figure that out, I'd be able to tweak it for other uses.