title: Creating a dynamic start and end point in a range to paste into
body: <p>I'm fairly new to VBA.</p>
  <p>I have a piece of code which copied all rows that have numeric data in Column K from one spreadsheet to another. This will be run on multiple spreadsheets, which have difference exercise names and job numbers.</p>
  <p>The exercise as job number only appear in one cell on the original sheet, hene why this is in a seperate line of code to the If statement checking if the values are numerical.</p>
  <p>I have the rows with numbers go in first. Then I add the exercise and job number into columns S and R. My initial code works fine except the range is given as .range(&quot;R2:2&quot; &amp; lastrow).Value so while it does copy in what I want it to copy it always starts pasting from R2, so all the rows that were Exercise 1 are now Exercise 2. This will happen every time I copy anything in.</p>
  <p>I then tried defining firstrow and lastrow, but I get error1004.</p>
  <p>This is my original code which overwrites:</p>
  <pre><code>'Import job number and exercise  
  Dim jobnumber As String  
  Dim exercise As String  
  jobnumber = ActiveSheet.Range(&quot;S3&quot;).Value  
  exercise = ActiveSheet.Range(&quot;B4&quot;).Value  
  lastrow = Workbooks(&quot;GQE Asana Import&quot;).Sheets(&quot;Sheet1&quot;).Cells(Rows.Count, &quot;K&quot;).End(xlUp).Row  
      Workbooks(&quot;GQE Asana Import&quot;).Sheets(&quot;Sheet1&quot;).Range(&quot;R2:R&quot; &amp; lastrow).Value = jobnumber  
      Workbooks(&quot;GQE Asana Import&quot;).Sheets(&quot;Sheet1&quot;).Range(&quot;S2:S&quot; &amp; lastrow).Value = exercise  
  </code></pre>
  <p>This is the code where I tried to make the start range i.e. &quot;R2:R&quot; dynamic which gives the 1004 error</p>
  <pre><code>'Import job number and exercise  
  Dim jobnumber As String    
  Dim exercise As String  
  Dim firstrowjn As Integer  
  Dim firstrowex As Integer  
  jobnumber = ActiveSheet.Range(&quot;S3&quot;).Value  
  exercise = ActiveSheet.Range(&quot;B4&quot;).Value  
  lastrow = Workbooks(&quot;GQE Asana Import&quot;).Sheets(&quot;Sheet1&quot;).Cells(Rows.Count, &quot;K&quot;).End(xlUp).Row  
  firstrowjn = Workbooks(&quot;GQE Asana Import&quot;).Sheets(&quot;Sheet1&quot;).Cells(Rows.Count, &quot;R&quot;).End(xlUp).Row + 1  
  firstrowex = Workbooks(&quot;GQE Asana Import&quot;).Sheets(&quot;Sheet1&quot;).Cells(Rows.Count, &quot;S&quot;).End(xlUp).Row + 1  
      Workbooks(&quot;GQE Asana Import&quot;).Sheets(&quot;Sheet1&quot;).Range(firstrowjn &amp; lastrow).Value = jobnumber  
      Workbooks(&quot;GQE Asana Import&quot;).Sheets(&quot;Sheet1&quot;).Range(firstrowex &amp; lastrow).Value = exercise  
  </code></pre>
  <p>I've stepped through it and firstrowjn etc all pick up the right row numbers. It just seems to break at the point of adding the cell value.</p>
  <p>I have tried using ranges at the set keyword instead of integers, I have tried cells() instead of range() but while this doesn't error it also doesn't change the values. I have tried Range(Cells...) but it doesn't like it being defined that way either. I tried a for each loop but that takes considerably longer to complete and again I can't get round the issue of defining my ranges dynamically.</p>
  <p>How do I make the starting range - &quot;R2:R&quot; dynamic like the last row is?</p>
  <p>Notes: last row is set further back in the code, and is an integer. I use column K because it has the first lot of data in so that is how many time the job no/exercise need to be in Columns R and S. The macro will be run out of each sheet with the data as the number will vary each month, which is why I use activesheet.</p>
  <p>I think that covers everything. Thanks in advance for your help!</p>