How would you refence 'ActiveSheet' instead of the actual sheet in the following section of code?

[xcelnovice101]

[M2].FormulaArray = "=MAX(IF('Historical Orders'!$B$2:$B$1048576='Form (2)'!M1,'Historical Orders'!$K$2:K1048576+1,1))"

 

[par60056]

try:
[M2].FormulaArray = "=MAX(IF('Historical Orders'!$B$2:$B$1048576='" & activesheet.Name & "'!M1,'Historical Orders'!$K$2:K1048576+1,1))"

 

[xcelnovice101]

try:
[M2].FormulaArray = "=MAX(IF('Historical Orders'!$B$2:$B$1048576='" & activesheet.Name & "'!M1,'Historical Orders'!$K$2:K1048576+1,1))"

Thanks a lot par60056! That did the trick.

 

[par60056]

You are welcome. Just remember you a building a string to represent the formula so you can use all the functions to build the string.

 

[Jonmo1]

Perhaps you can just remove the sheet reference all toghether..
If the sheet you want to reference is the sheet that will contain the formula...then no sheet reference is required..

[M2].FormulaArray = "=MAX(IF('Historical Orders'!$B$2:$B$1048576=M1,'Historical Orders'!$K$2:K1048576+1,1))"

 

[par60056]

I did not look at where the formula was being assigned and assumed the range that the formula is being assigned to could be on a sheet other than the active sheet.

 

[Jonmo1]

I'm taking the following assumption:
Given that there is no sheet specified in the code for where the formula is going, then by default it goes to whatever is the currently active sheet..

[M2].FormulaArray = "...

So if the formula goes on the currently active sheet, and we want that cell reference to refer to the currently active sheet...
Then we don't need the sheet reference at all.