VBA Excel: change offset based on active sheet

Question

I'm trying to figure out how i can make a offset based on the Active sheet number.

Example:

Right now in sheet number 2 in Cell "B1" I have a number set of 17000 On the same sheet at B8:B I have a column of numbers going down with certain values that I would like to add up to my base of 17000. Once I make a new sheet I want "A1" To have that value of the other 2 numbers added up.

I have a Code that "Fills in" The active sheet that I'm using.

But how could I make it that in each new sheet it will go 1 position down in column B8:B

So sheet 2 has the values that will be used.

• New sheet nmbr 5 gets created which will need sheet 2 "B1" + "B8"
• New sheet nmbr 6 gets created which will need sheet 2 "B1" + "B9"
• New sheet nmbr 7 gets created which will need sheet 2 "B1" + "B10"

And so on and so on.

Sub KnopKlik()

Dim WB As Workbook
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WS3 As Worksheet
Dim Active As Worksheet
Dim Titel1
Dim Titel2

 Set WB = ActiveWorkbook
 Set WS1 = WB.Sheets(1)
 Set WS2 = WB.Sheets(2)
 Set WS3 = WB.Sheets(3)
 Set Active = WB.ActiveSheet
 Set MC = Active.Range("B9")

 Titel1 = WS2.Range("B1") 'Base number of 17000
 Titel2 = WS2.Range("B8") 'Has to be added up to 17000 depending on sheet number
 column1 = Sheets(3).Cells(1, 3).Value

 Application.ScreenUpdating = False 

'============================================================
Sheets(1).Visible = True ' Activate Sheets
Sheets(2).Visible = True
Sheets(3).Visible = True

Active.Select
ActiveSheet.Range("A1").Value = "Unit " & (Titel1 + Titel2)

'This is the line that is suppose to write the question i asked.

'=============================================================================
' Between these lines is a bunch of code i left out cause its irrelivant to the question.
'=============================================================================


Application.ScreenUpdating = True

Active.Select

Sheets(1).Visible = xlVeryHidden
Sheets(3).Visible = xlVeryHidden
Sheets(4).Visible = xlVeryHidden

MsgBox ("Done")

End Sub

I hope the question isn't to hard to understand. I got what i want exactly in my head but i find it hard to explain in English :P

Answer 1

Ok, try

 Titel2 = WS2.cells(4 + activesheet.index,2)