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

[squarah]

Hi all

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.

[/COLOR][COLOR=#000000][FONT=Consolas]Sheets[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"temp_for_calcs"[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]).[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]Columns[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]([/FONT][/COLOR][COLOR=#800000][FONT=Consolas]"a:i"[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]).[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]Copy[/FONT][/COLOR]

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">tables_sheet.[COLOR=#00008B]Select[/COLOR]Cells([COLOR=#800000]1[/COLOR], table_column).[COLOR=#00008B]Select[/COLOR]Selection.PasteSpecial xlPasteValues [COLOR=#808080]'copy and paste values to tables <<<<<<<<<<<< breaks here[/COLOR][COLOR=#00008B]Call[/COLOR] ClearClipboardp = p + [COLOR=#800000]3[/COLOR] [COLOR=#808080]' loop variables change to account for previous table when pasting[/COLOR]flag_column = flag_column + [COLOR=#800000]3[/COLOR]table_column = table_column + [COLOR=#800000]10[/COLOR]Application.DisplayAlerts = [COLOR=#800000]False[/COLOR] [COLOR=#808080]' turns off warning about deleting sheets[/COLOR]temp_for_calcs.Delete [COLOR=#808080]'delete temp for calcs so cell refs are the same for the next DMA table[/COLOR] </code></pre>[COLOR=#000000][FONT=Consolas]Application[/FONT][/COLOR][COLOR=#000000][FONT=Consolas].[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]DisplayAlerts [/FONT][/COLOR][COLOR=#000000][FONT=Consolas]=[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] [/FONT][/COLOR][COLOR=#800000][FONT=Consolas]True[/FONT][/COLOR][COLOR=#333333]

any insight into how I can fix this?
thanks a lot

 

[VoG]

Cross posted excel cannot complete this task when paste values from temp sheet

Please read Excelguru Help Site - A message to forum cross posters