sumif formula with sheet number

[HKB]

I'm trying to do a SUMIFS calculation in VBA. It works fine with worksheet name.

I want it to run by worksheet number. so user can change the worksheet name.

Worksheet name "TOTAL_SALES".
Please Help.

Here is code

Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS('TOTAL_SALES'!R3C17:R22000C17,'TOTAL_SALES'!R3C2:R22000C2,RC[-10],'TOTAL_SALES'!R3C4:R22000C4,RC[-8],'TOTAL_SALES'!R3C5:R22000C5,RC[-7],'TOTAL_SALES'!R3C6:R22000C6,RC[-6],'TOTAL_SALES'!R3C7:R22000C7,RC[-5])"

 

[NARAYANK991]

Hi ,

Please post the correct , working worksheet formula as you would enter it in a worksheet cell / range in A1 style.

Narayan

 

[John Jairo V]

Hi, to both of you!

Try this:

Range("K2") = Replace("=SUMIFS(#!$Q$3:$Q$22000,#!$B$3:$B$22000,A2,#!$D$3:$D$22000,C2,#!$E$3:$E$22000,D2,#!$F$3:$F$22000,E2,#!$G$3:$G$22000,F2)", "#", Sheets(2).Name)

Replace Index Number for your Index sheet. Blessings!