Fetch data from sheet having same name as of data heading

[caabdul]

Hi,

Here I am with a simple problem.

Is it possible with vba to pull data from other sheets based on criteria of matching the heading name and sheet name.
I have attached the file. Sheet1 contains the cells (C5 to N13) that need to be filled using vba code.

Thank you all for your assistance.

[JBeaucaire]

No VBA is needed. Put this formula in C5, then copy down and across:

=VLOOKUP($B5, INDIRECT("'" & C$4 & "'!B:C"), 2, 0)

[caabdul]

Hi Thank you for your assistance. About 90% of the problem is resolved that I have the right formula to be used.

I was wondering if there is any way that I can use VBA to enter formula in each cell of the given space in sheet1? For example we may use End(xlDown) etc.

Thanks

[JBeaucaire]

Yes, you can turn on the macro recorder and let it record you entering that formula in the first cell, that gives you the VBA version of the formula and the starting cell.

Then you can use end(xlDown) as a method of targeting the entire range, or even try the .Resize(?, ?) method. For resize you could countA the number of values in column B, then subtract 1 to get the number of rows, do the same for row 4 to get the number of columns, then put those values into the RESIZE and your formula would be applied to that entire resized range expanding out from C5.

Give it a good try.