title: VBA Excel: change offset based on active sheet
body: <p>I'm trying to figure out how i can make a offset based on the Active sheet number.</p>
  
  <p>Example: </p>
  
  <p>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.</p>
  
  <p>I have a Code that "Fills in" The active sheet that I'm using.</p>
  
  <p>But how could I make it that in each new sheet it will go 1 position down in column B8:B</p>
  
  <p>So sheet 2 has the values that will be used.</p>
  
  <ul>
  <li>New sheet nmbr 5 gets created which will need sheet 2 "B1" + "B8"</li>
  <li>New sheet nmbr 6 gets created which will need sheet 2 "B1" + "B9"</li>
  <li>New sheet nmbr 7 gets created which will need sheet 2 "B1" + "B10"</li>
  </ul>
  
  <p>And so on and so on.</p>
  
  <pre><code>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 " &amp; (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
  </code></pre>
  
  <p>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</p>