title: Excel VBA - Compile Error - Object Required
body: <p>This is my first post to this wonderful forum . I am new to VBA and have been writing my first meaningful macro beside doing some practice while learning. </p>
  
  <p>While the WIP code is reproduced below, let me give you a background. </p>
  
  <p>Have a a sheet to calculation investment maturity  based on various inputs like Term of Payment and term of Maturity. </p>
  
  <p>This sheet has all the formule in place to compute the maturity value. </p>
  
  <p>Now on sheet 2, I am creating a matrix structure with a intersection of payment term and term of maturity. For ex combination of 1 Yr of Payment term with maturity ( 1...25) and next Payment term and so on.</p>
  
  <p>I am writing a macro which will pick up the user choice from sheet 1 and then loop the macro in matrix and capture the result and paste it to sheet two . </p>
  
  <p>I am still far away from what i have to achive but got a code in first few line of selecting and setting the payment term and maturity terms</p>
  
  <p>Error is Compile error- Object required. I am not able to figure out what it it. WIP code is reproduced below</p>
  
  <pre><code>Private Sub Simulation_Click()
  Dim PPT As String
  Dim Term As String
  MsgBox (" The Programme is starting now")
  
  Set PPT = Workbooks("himanshu.xlsm").Worksheet("Sheet1").Range("G2").Value
  Set Term = Workbooks("himanshu.xlsm").Worksheet("Sheet1").Range("G3").Value
  
  For PPT = 1 To PPT  
      For Term = 1 To Term  
       ' Do something here  
      Next Term  
  Next PPT  
  
  End Sub
  </code></pre>
  
  <p>Please help
  EDIT # 1- September 19,2017, 3.10 AM IST
  Hi Experts, 
  Finally my first VBA code is running . Its slow but its running. One last problem
  I have issue in Code line "Workbooks("himanshu.xlsm").Sheets("Sheet1").Range("J2").Copy Workbooks("himanshu.xlsm").Sheets("What if").Cells(cols, Rowsh)" where while to source carries the right value( checked in debug Mode) but when it is pasted to target, it is pasted as 0. When I checked the target sheet , somehow it is showing me a formula (= Max( R1C1 :X1Y1). Please note that from where I am copyiong the value ( Source) , its a excel calculation which is arrived as a formula of selecting max of col J . I suspect that code is copying the formula instead of value. Please advice</p>
  
  <p><div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false">
  <div class="snippet-code">
  <pre class="snippet-code-js lang-js prettyprint-override"><code>Option Explicit
  Sub macro21()
  'MsgBox ("Start")
  Dim i As Integer, j As Integer
  Dim PPT As Integer
  Dim Term As Integer
  Dim pptrange As Range    ' Address of the Cell where PPT is maintained in Sheet 1
  Dim termrange As Range   ' Address of the Cell where Term is maintained in Sheet 1
  Dim cols As Integer
  Dim Rowsh As Integer
  
  PPT = 3
  Term = 5
  cols = 3
  Rowsh = 3
  For i = 1 To PPT
  
      For j = 1 To Term
          
          Set pptrange = Workbooks("himanshu.xlsm").Sheets("Sheet1").Range("G2")  ' Set pptrange and termrange locations
          Set termrange = Workbooks("himanshu.xlsm").Sheets("Sheet1").Range("G3")   ' Set pptrange and termrange locations
          pptrange.Value = i
          termrange.Value = j
          ***Workbooks("himanshu.xlsm").Sheets("Sheet1").Range("J2").Copy Workbooks("himanshu.xlsm").Sheets("What if").Cells(cols, Rowsh)***
  '        MsgBox ("Value is " &amp; Cells(cols, Rowsh).Value)
          MsgBox ("Value is " &amp; Workbooks("himanshu.xlsm").Sheets("Sheet1").Range("J2"))
          
          
          If j &lt; Term Then
              cols = cols
              Rowsh = Rowsh + 1
          End If
      Next j
      cols = cols + 1
      Rowsh = 3
  Next i
  
  End Sub</code></pre>
  </div>
  </div>
  </p>
  
  <p>I have issue in Code line "Workbooks("himanshu.xlsm").Sheets("Sheet1").Range("J2").Copy Workbooks("himanshu.xlsm").Sheets("What if").Cells(cols, Rowsh)" where while to source carries the right value( checked in debug Mode) but when it is pasted to target, it is pasted as 0. When I checked the target sheet , somehow it is showing me a formula (= Max( R1C1 :X1Y1). Please note that from where I am copyiong the value ( Source) , its a excel calculation which is arrived as a formula of selecting max of col J . I suspect that code is copying the formula instead of value. Please advice</p>