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.
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: " & 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: " & maxRow
inputSheet.Range("A1").Resize(maxRow, maxColumn).Copy
With outputSheet
.Activate
.Range("A1").Select
.Paste Link:=True
End With
End Sub
XLSX files store the cell contents as a string. But they don't clean up after themselves very good. So if you delete something, it doesn't always reduce the file size.
If you merely copy the sheet it shouldn't become as big as if you generate links to all the cells. Thats because each link is stored as a separate string.
http://www.ozgrid.com/Excel/ExcelProblems.htm
It's very possible that the original sheet has some modification that reaches to the very last row. Then when you copy it as links it goes crazy because it is generating millions of links. Check the value of maxColumns and maxRows.