Macro : Copy/Paste range of value with a Variable starting point

[Helpco]

Hi,

Thank you for reading this post.

I have a table containing the result for different races I extract from another software, compared over 2 year (2011, 2012)

The table look a bit like that :
[TABLE="width: 711"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 4"]2011[/TD]
[TD][/TD]
[TD="colspan: 4"]2012[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Alpha[/TD]
[TD]Bravo[/TD]
[TD]Charlie[/TD]
[TD]Romeo[/TD]
[TD]TOTAL [/TD]
[TD]Alpha[/TD]
[TD]Bravo[/TD]
[TD]Charlie[/TD]
[TD]Romeo[/TD]
[TD]TOTAL [/TD]
[/TR]
[TR]
[TD]Race 1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]354[/TD]
[TD="align: right"]968[/TD]
[TD="align: right"]95[/TD]
[TD="align: right"]876[/TD]
[TD="align: right"]2293[/TD]
[/TR]
[TR]
[TD]Race 2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]45245[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]242[/TD]
[TD="align: right"]45516[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]687[/TD]
[TD="align: right"]247856[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]248912[/TD]
[/TR]
[TR]
[TD]Race 3[/TD]
[TD="align: right"]52468[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]53034[/TD]
[TD="align: right"]6987[/TD]
[TD="align: right"]7858[/TD]
[TD="align: right"]7857[/TD]
[TD="align: right"]786547[/TD]
[TD="align: right"]809249[/TD]
[/TR]
[TR]
[TD]Race 4[/TD]
[TD="align: right"]6958[/TD]
[TD="align: right"]74785[/TD]
[TD="align: right"]78785[/TD]
[TD="align: right"]7578[/TD]
[TD="align: right"]168106[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]7585[/TD]
[TD="align: right"]7855[/TD]
[TD="align: right"]7557[/TD]
[TD="align: right"]23072[/TD]
[/TR]
</tbody><colgroup><col span="7"><col><col span="3"></colgroup>[/TABLE]


The problem is, on the software I extract the data from, the number of columns can change (let's say there is an additional pilot).

In a new tab, I want to have only the result for the year 2012 (I use a color code to show if it was better than 2011, I don't need the actual result to be shown). I tried to use the function Indirect but I can't get it to work between two different worbook, the fuction I tried is :

=Tab1!INDIRECT($B$1&"7") Wich I want it to return Y7 of Tab1. (I typed Y in B1)

but as I say it does not work, any other idea ?????? a sort of Vlookup ???

thank you very much for your help, much appreciated

Co

 

[sheetspread]

=INDIRECT("Tab1!"&B1&7)

 

[AlphaFrog]

=Tab1!INDIRECT($B$1&"7") Wich I want it to return Y7 of Tab1. (I typed Y in B1)

This syntax could work for your INDIRECT formula.

=INDIRECT("Sheet1!" & $B$1 & "7")