title: Autofill with variable number of rows
body: <p>I am trying to automate a monthly report in Excel with VBA where the final autofill is based on variable number of rows that change monthly with the change in # of clients. For example: if this month I have 25 clients, I could easily hardcode that I want to create 25 rows of my report. But in the next month when I have 30 clients, my code still would say 25 and therefore would miss five clients. </p>
  
  <p>One solution I am trying to work on is to count the number of rows and apply that to the autofill formula. Trying to find code that would read like this: Autofill Range A1 to C "Number of rows in Worksheet X."</p>
  
  <p>Now I know this clearly doesn't work, but this was my poor best attempt:</p>
  
  <pre><code>Sub FillReport()
  Sheets("Sheet1").Select
  Range("D1").Select
  ActiveCell.FormulaR1C1 = "=COUNTA(A:A)"
  Sheets("Sheet2").Select
  Range("A1:C1").Select
  Selection.Autofill Destination:=Range("A1:C(Sheets("Sheet1").Range("D1").Select)")
  </code></pre>
  
  <p>I didn't expect this work but wanted to try it anyway. As you can see I am quite the VBA novice...</p>
  
  <p>If anyone has any insight that would be very helpful as I feel like there should be a way to accomplish this that I am just completely missing on my own. </p>
  
  <p>Thanks.</p>