Need help with VBA for Index Match from one table to another

[spgexcel]

Hi Guys,

I am trying to formulate a VBA code for index match formula applied on a table in my worksheet.
My requirement is to pull data from Table1 to Table 2 based on the change in order of Column1 in table 2.
I applied normal formula to it. It works but I was wondering if you could help me get a vba code for the same so that my file size is reduced by some bits.
I tried a lot of threads but did not find a suitable answer that has multiple columns to be populated based on criteria in 1st column.

Table1	Region	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
	USA	89%	75%	82%	80%	59%	28%	87%	39%	81%	62%	76%	32%
	Germany	70%	65%	98%	100%	81%	49%	91%	36%	61%	62%	34%	23%
	France	44%	93%	55%	84%	71%	67%	96%	26%	54%	29%	46%	70%
	India	63%	56%	67%	56%	67%	99%	69%	66%	29%	65%	91%	96%
	Japan	29%	47%	41%	62%	33%	32%	60%	35%	64%	40%	55%	33%
	UK	54%	100%	48%	78%	32%	21%	49%	82%	86%	46%	23%	33%

Table2	Region	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18	Nov-18	Dec-18
	Germany	70%	65%	98%	100%	81%	49%	91%	36%	61%	62%	34%	23%
	France	44%	93%	55%	84%	71%	67%	96%	26%	54%	29%	46%	70%
	France	44%	93%	55%	84%	71%	67%	96%	26%	54%	29%	46%	70%
	India	63%	56%	67%	56%	67%	99%	69%	66%	29%	65%	91%	96%
	UK	54%	100%	48%	78%	32%	21%	49%	82%	86%	46%	23%	33%
	Japan	29%	47%	41%	62%	33%	32%	60%	35%	64%	40%	55%	33%

Your help is highly appreciated.

Thanks
Sumant

 

[Saurabhj]

Hi Sumant,

"change in order of Column1 in table 2" meaning sorting of column, right ?

Thanks,
Saurabh

 

[Saurabhj]

Hi Sumant,

Please check below code. Add a button in a sheet and add below macro to button.

Assuming Table 1 is in Sheet1 and Table 2 is in Sheet2.

Sub calculate()
            Dim totalRows As Integer, rowno As Integer, colno As Integer
             totalRows = WorksheetFunction.CountA(Sheets("Sheet2").Range("A:A"))

            Sheets("Sheet2").Cells(2, 2).Formula = "=INDEX(Sheet1!$B$1:$M$7,MATCH(Sheet2!$A2,Sheet1!$A:$A,0),MATCH(Sheet1!B$1,Sheet1!$B$1:$M$1,0))"
            Sheets("Sheet2").Cells(2, 2).Activate
            ActiveCell.AutoFill Destination:=ActiveCell.Range("A1:L1"), Type:= _
            xlFillDefault
            ActiveCell.Range("A1:L1").Select
            Selection.AutoFill Destination:=ActiveCell.Range("A1:L" & totalRows)

End Sub

Thanks,
Saurabh