title: Paste values in dynamic range excel vba
body: <p>I am writing a script where I want to enable a search in a Database, presenting the results of the search queries in a different worksheet (which I have named Results), so that users do not have access to the whole database at the same time.</p>
  
  <p>In order to do this I want to copy values from the "Database" worksheet into the "Results" worksheet. I have succeeded in selecting the right data from the "Database", in respect to any specific search criteria. I did this with the following code:</p>
  
  <pre><code>With Sheets("Database")
     .Range(.Cells(i, 1), .Cells(i, 9)).Copy
  End With
  </code></pre>
  
  <p>Now I want to paste the results into the "Results" spreadsheet and I have done so by writing the following:</p>
  
  <pre><code>Sheets("Results").Range("B600").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
  </code></pre>
  
  <p>By doing this, I don't quite understand:</p>
  
  <ul>
  <li><p>if I have strictly defined the paste range as between the first empty row and B600 or;</p></li>
  <li><p>if I am just defining the beginning of the paste range and, in the case that the search results exceed the 600th row, they will still be pasted after this row.</p></li>
  </ul>
  
  <p>I ask this because, as the database grows, I will certainly need to guarantee a paste range greater than B600.</p>
  
  <p>I have researched on it but cannot seem to be absolutely sure of what I have done exactly. I must say that I know that the first empty row in the "Results" database will always be 12. In this case, I know that I basically want to paste the search results from the 12th row on. Maybe there is a more straight-forward way to do this.</p>
  
  <p>This is the entire code, for reference:</p>
  
  <pre><code>Private Sub SearchButton_Click()
  
  'This is the search function
  
  '1. declare variables
  '2. clear old search results
  '3. Find records that match criteria and paste them
  
  Dim country As String
  Dim Category As String
  Dim Subcategory As String
  Dim finalrow As Integer
  Dim i As Integer 'row counter
  
  
  'Erase any entries from the Results sheet
  Sheets("Results").Range("B10:J200000").ClearContents
  
  'Deformat any tables in the Results sheet
  For Each tbl In Sheets("Results").ListObjects
      tbl.Clear
  
      Next
  
  'Define the user-inputed variables
  country = Sheets("Results").Range("D5").Value
  Category = Sheets("Results").Range("D6").Value
  Subcategory = Sheets("Results").Range("D7").Value
  finalrow = Sheets("Database").Range("A" &amp; Rows.Count).End(xlUp).Row
  
  'If statement for search
  
  'For every variable i, start comparing from row 2 until the final row
  For i = 2 To finalrow
  
      'If the country field is left empty
      If country = "" Then
          Sheets("Results").Range("B10:J200000").Clear
          MsgBox "You must select a country in order to search the database. Please do so in the drop-down list provided."
          Sheets("Results").Range("D5").ClearContents
          Sheets("Results").Range("D6").ClearContents
          Sheets("Results").Range("D7").ClearContents
          Exit Sub
  
      'If the country field is filled in and there results from the search made
      ElseIf Sheets("Database").Cells(i, 1) = country And _
          (Sheets("Database").Cells(i, 3) = Category Or Category = "") And _
          (Sheets("Database").Cells(i, 4) = Subcategory Or Subcategory = "") Then
  
              'Copy the headers of the table
              With Sheets("Database")
              .Range("A1:I1").Copy
              End With
              Sheets("Results").Range("B10:J10").PasteSpecial
  
              'Copy the rows of the table that match the search query
              With Sheets("Database")
              .Range(.Cells(i, 1), .Cells(i, 9)).Copy
              End With
              Sheets("Results").Range("B600").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
  
      'Hides search form
      Me.Hide
  
      End If
  
  Next i
  
  'Toggle Results sheet
  Sheets("Results").Activate
  
  'Format results as a table
  Set rng = Range(Range("B10"), Range("B10").End(xlUp).SpecialCells(xlLastCell))
  Set table = Sheets("Results").ListObjects.Add(xlSrcRange, rng, , xlYes)
  table.TableStyle = "TableStyleMedium13"
  
  Range("B11").Select
  
  'Make Excel window visible
  Application.Visible = True
  
  End Sub
  </code></pre>
  
  <p>Thank you very much for your help.</p>