CODEWITHSUNDEEP
vbaexcel
squar_o
squar_o

Reputation: 567

cannot complete this task with available resources paste values from temp sheet

I'm running a macro in Excel 2010 that filters unique values, does v lookups and simple calculations then pastes a table from a temporary sheet into a permanent sheet. I am repeating this process 14 times in total. However, it will only work for a maximum of two iterations before I get the error above. It appears to be at paste values that it breaks.

Sheets("temp_for_calcs").Columns("a:i").Copy
tables_sheet.Select
Cells(1, table_column).Select
Selection.PasteSpecial xlPasteValues 'copy and paste values to tables <<<<<<<<<<<< breaks here
Call ClearClipboard

p = p + 3 ' loop variables change to account for previous table when pasting
flag_column = flag_column + 3
table_column = table_column + 10

Application.DisplayAlerts = False ' turns off warning about deleting sheets
temp_for_calcs.Delete 'delete temp for calcs so cell refs are the same for the next DMA table
Application.DisplayAlerts = True

any insight into how I can fix this?

thanks a lot

Upvotes: 2

Views: 317

Answers (1)

S Meaden
S Meaden

Reputation: 8270

You seem to be copying a lot to the clipboard but only pasting the values. Might I suggest that you grab values only use .Value. Also perhaps use .UsedRange to cut dimension.

Sub S()
    'ASSUMPTIONS - you did not supply these so your code did not compile
    Dim table_column, p, flag_column
    table_column = 1
    'END OF ASSUMPTIONS

    Dim rngCopiable As Excel.Range
    Set rngCopiable = Sheets("temp_for_calcs").UsedRange.Columns("a:i")  'UsedRange to cut down size

    Dim lColumnCount As Long
    lColumnCount = rngCopiable.Columns.Count

    Dim lRowCount As Long
    lRowCount = rngCopiable.Rows.Count


    Dim vValues As Variant
    vValues = rngCopiable.Value  'mass copying

    tables_sheet.Select

    Cells(1, table_column).Resize(lRowCount, lColumnCount).Value = vValues  'mass pasting

    vValues = Empty   'Releases Memory

    'Cells(1, table_column).Select
    'Selection.PasteSpecial xlPasteValues 'copy and paste values to tables <<<<<<<<<<<< breaks here
    'Call ClearClipboard

    p = p + 3 ' loop variables change to account for previous table when pasting
    flag_column = flag_column + 3
    table_column = table_column + 10

    Application.DisplayAlerts = False ' turns off warning about deleting sheets
    temp_for_calcs.Delete 'delete temp for calcs so cell refs are the same for the next DMA table
    Application.DisplayAlerts = True

End Sub

Upvotes: 1

Related Questions