title: Large File Size Copy Ranges with VBA
body: <p>I have a routine that duplicates a sheet by generating cell references to the old sheet. My problem is for that when I run this routine on a three sheets in a workbook sized 280KB it balloons up to 80MB. If I copy and paste the sheet values to remove the formulas, the workbook stays the same size. If I delete the sheets and save then it returns to it's original size. Is there something I'm missing in the way VBA handles memory that is causing this issue? I've already tried setting all of the variables to nothing at the end of the routine.</p>
  
  <pre><code>Public Sub CopySheetFormulas(inputSheet As Worksheet, outputSheet As Worksheet)
  Dim rowCounter As Long
  Dim columnCounter As Long
  Dim maxRow As Long
  Dim maxColumn As Long
  
  maxColumn = inputSheet.Cells.Find(What:="*", After:=inputSheet.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
  SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
  
  Debug.Print "Max Column: " &amp; maxColumn
  
  maxRow = inputSheet.Cells.Find(What:="*", After:=inputSheet.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
  SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
  
  Debug.Print "Max Row: " &amp; maxRow
  
  inputSheet.Range("A1").Resize(maxRow, maxColumn).Copy
  
  With outputSheet
      .Activate
      .Range("A1").Select
      .Paste Link:=True
  End With
  
  End Sub
  </code></pre>