Link data from multiple worksheets which uses the same cells values

I have a problem.

I want to extract data to create a list from workbook (B) into another workbook (A).

NB: Using the "=" sign is hard and long to extract data.

So my point is:

On workbook (A), I have a list of countries names from row A2 to row A51

Next. On workbook (B). Each country has it own "worksheet". From worksheet #1 to Worksheet #50 and all the values are in cells "F2 & G2" respectively.

I want excel to grab each worksheet values in "F2 & G2" in workbook (B) and paste it as a list in workbook (A) in worksheet (1).

How can I do that?

Hello (you may start a message with something like that...),

A possibility :

Sub ImportData()

Dim Rw As Long, RwMax As Long, WbSource As Workbook, Country As String

On Error Resume Next

Set WbSource = Workbooks.Open(Filename:="C:\User\FullPath\WorkbookBname.xlsx") 'Filename to adapt
With ThisWorkbook.Sheets(1)
    RwMax = .Range("A" & Rows.Count).End(xlUp).Row 'Last row
    For Rw = 1 To RwMax 'loop on rows
        Country = .Range("A" & Rw)
        .Range("B" & Rw) = WbSource.Sheets(Country).Range("F2")
        .Range("C" & Rw) = WbSource.Sheets(Country).Range("G2")
    Next
End With
WbSource.Close False
MsgBox "Work done !"

End Sub

Each Worksheet in Workbook B have to be named like the searched country in column 1 Workbook A.

Hello. I sorry for my manners. Thanks for your reply.

I did change the file name, "(Filename:="C:\User\FullPath\WorkbookBname.xlsx")" to

(Filename:="C:\Users\Corey\Desktop\Anton Heatwave\PMI").

and I made and error to state where I want to paste the values.

from:

.Range("B" & Rw) = WbSource.Sheets(Country).Range("F2")

.Range("C" & Rw) = WbSource.Sheets(Country).Range("G2")

To:

.Range("C" & Rw) = WbSource.Sheets(Country).Range("F2")

.Range("D" & Rw) = WbSource.Sheets(Country).Range("G2")

But somehow, it don't seems to work.

How can I get around this?

Thanks in advance

Filename may include full path (like C:\Users\Corey\Desktop\Anton Heatwave\PMI\) and workbook name (like WorkbookB.xlsx).

You can just test this code to be sure that the workbook is correctly opened :

Sub Test1()

    Workbooks.Open(Filename:="C:\User\FullPath\WorkbookName.xlsx") 'Filename to adapt

End Sub

The test code don't seems to work.

Error states: Compile error: Syntax error.

How can I get around this?

corriedtoppin a écrit :

The test code don't seems to work.

Error states: Compile error: Syntax error.

How can I get around this?

Paste here the code you tested, please.

I'm not a soothsayer !

Sub Test1()

Workbooks.Open(Filename:="C:\Users\Corey\Desktop\Anton Heatwave\PMI") 'Filename to adapt

End Sub

The above code

You are just reading half part of my responses...

Pedro22 a écrit :

Filename may include full path (like C:\Users\Corey\Desktop\Anton Heatwave\PMI\) and workbook name (like WorkbookB.xlsx).

Your actual filename is not valid.

Ohhh. I'm sorry going to try

This is the file name:

Sub Test1()

Workbooks.Open(Filename:="C:\Users\Corey\Desktop\Anton Heatwave\PMI") 'Countries Indicators #1 NSB (1)

End Sub

corriedtoppin a écrit :

This is the file name:

Sub Test1()

Workbooks.Open(Filename:="C:\Users\Corey\Desktop\Anton Heatwave\PMI") 'Countries Indicators #1 NSB (1)

End Sub

This is still not a valid filename...

The right syntax is simple : Complete Path + \ + Workbook name + Extension

I gave you an example : C:\User\FullPath\WorkbookName.xlsx

Your filename doesn't look like to this one !

I don't know. That's the name of the excel file: Countries Indicators #1 NSB (1)

Sub Test1()

Workbooks.Open(Filename:="C:\Users\Corey\Desktop\Anton Heatwave\PMI") 'Countries Indicators #1 NSB (1).xlsx

End Sub

I am only getting and error.

Sub Test1()

Workbooks.Open (Filename: = "C:\Users\Corey\Desktop\Anton Heatwave\PMI\Countries Indicators #1 NSB(1).xlsx") 'Filename to adapt

End Sub

Still nothing

corriedtoppin a écrit :

Sub Test1()

Workbooks.Open (Filename: = "C:\Users\Corey\Desktop\Anton Heatwave\PMI\Countries Indicators #1 NSB(1).xlsx") 'Filename to adapt

End Sub

Still nothing

Hello,

That one look like a valid full name. If that's not working anymore, I can't help you.