Macro Problem

L

lj

I am running into a problem when I try and run a macro that copies a
sheet that has a pivot table in it and moves it to another folder and
then copys-past special-values the sheet. When I try and run macros
that do this I get an error message when the macro finishes processing
that says "Excel cannot complete this task with available resources.
Choose less data or close other applications". Do you have any
suggestions or workarounds I can use to avoid this problem? Below is
an example of the visual basic I am using in the macro.

Sheets(Array("Calculations", "Trading_Profit")).Select
Sheets("Trading_Profit").Activate
Sheets(Array("Calculations", "Trading_Profit")).Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
 
D

Dave Peterson

Maybe if you did each sheet separately, it would be less intensive????

Dim Wks as worksheet
Sheets(Array("Calculations", "Trading_Profit")).Copy
for each wks in activeworkbook.worksheets
with wks.cells
.copy
.pastespecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
end with
next wks
Application.CutCopyMode = False

But that's just a guess.
 
Top