VBA macro making out of memory issue

Question

I have "Out of memory" issue with my Excel and VBA when I try to run macro below

Sub CopyPaste() ' macro to copy dynamic range
Dim lRow As Long
Dim sht As Worksheet
Set sht = Sheets("SQL")
sht.Activate
lRow = sht.Cells(sht.Rows.Count, 2).End(xlUp).Row
sht.Range("A1:Q" & lRow).Copy
Workbooks.Add
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:Q").EntireColumn.AutoFit
End Sub

My idea is to copy dynamic range from SQL tab in excel and paste to new workbook as values, columns to be autofit and all cells centered. I have powerful machine at home, tried to reboot it and restart excel just in case.

Maybe reading [How to avoid using select](https://stackoverflow.com/a/23913882/16578424) and [How to avoid copy/paste](https://stackoverflow.com/a/64611707/16578424) already helps - if you apply those adivses, the error will most likely be gone. — Ike, Dec 13 '22 at 09:53

FaneDuru · Answer 1 · 2022-12-13T11:04:47.327

Please, try the next adapted code. It does not activate, select anything. They are useless, only consuming Excel resources. Since you try copying only values, you also do not need using Clipboard:

Sub CopyPaste() ' macro to copy dynamic range
 Dim lRow As Long, sht As Worksheet

 Set sht = Sheets("SQL")
 lRow = sht.cells(sht.rows.count, 2).End(xlUp).row 'last row on B:B column

 Workbooks.Add
 With sht.Range("A1:Q" & lRow)
     ActiveSheet.Range("A1").Resize(.rows.count, .Columns.count).Value = .Value
 End With
 Columns("A:Q").EntireColumn.AutoFit
End Sub

If B:B is not the column you like to be the reference for the last used range cell, please change it using the necessary column number (instead of 2 in sht.cells(sht.rows.count, 2))

If you like using Clipboard, the next code will be suitable:

Sub CopyPaste() ' macro to copy dynamic range
  Sheets("SQL").Copy 'it creates a new workbook with THAT single sheet
 
  'in case of existing columns after Q:Q, use the next code to clear. If not, delete the next code lines:
  Dim lastCol As Long
  lastCol = ActiveSheet.cells(1, .ActiveSheet.Columns.count).End(xlToLeft).column
  If lastCol > 17 Then
        Range(cells(1, 18), cells(1, lastCol)).EntireColumn.Clear
  End If
End Sub

If no any column after Q:Q, the code may have only a code line...

VBA macro making out of memory issue

1 Answers1