Excel cannot complete this task with available resources. Choose less data or close other application. ActiveWorkbook.SaveAs

Question

I'm having problem on running the following VBA code on Excel 2010 or later, while it ran just fine on Excel XP. In later versions, a message box with the text "Excel cannot complete this task with available resources. Choose less data or close other application." appears and, on clicking "OK", Excel restarts.

Code:

Sub Calibrar()

    Dim PDFFile As String
    Dim XLSFile As String

    Application.ScreenUpdating = False
    Worksheets("Agendamento").Activate

    XLSFile = Range("W17") & "_" & Range("W19") & "_" & Range("E23") & "_" & "Agendamento" & ".xls"

    ActiveWorkbook.SaveAs Filename:="\\PMPA-FS3\DMAE-LabHid-Calibra-Agendamento$\Solicitações\" & Range("W17") & "_" & Range("W19") & "_" & Range("E23") & "_Agendamento" & ".xls"

    PDFFile = Range("W17") & "_" & Range("W19") & "_" & Range("E23") & "_" & Range("E11") & "_" & "Pedido" & ".xls"

    Worksheets("Agendamento").Activate

    Worksheets("Agendamento").Move

    ActiveWorkbook.SaveAs Filename:="\\PMPA-FS3\DMAE-LabHid-Calibra-Agendamento$\Pedidos\" & Range("W17") & "_" & Range("W19") & "_" & Range("E23") & "_" & Range("E11") & "_" & "Pedido" & ".xls"

    Worksheets.PrintOut Preview:=False, Copies:=1, ActivePrinter:="CutePDF Writer on CPW2:", PrToFileName:="", Collate:=False

    Workbooks(XLSFile).Activate

    Worksheets("Controle").Activate

    Workbooks(XLSFile).Sheets("Controle").Move After:=Workbooks(PDFFile).Sheets(1)

    Workbooks(XLSFile).Activate

    Worksheets("Análise Crítica").Activate

    ActiveWorkbook.SaveAs Filename:="\\PMPA-FS3\DMAE-LabHid-Calibra-AnaliseCritica$\" & Range("W15") & "_" & Range("W16") & "_" & Range("E20") & "_Analise Critica" & ".xls"

    ActiveWorkbook.Close Savechanges:=True
    ActiveWorkbook.Close Savechanges:=True

End Sub

When I ran this, all files were generated normally, but the message and restarting of Excel are quite annoying.

(EDIT: I forgot to mention that I ran this macro with Task Manager to see how much memory was used and I couldn't see any excessive consumption.)

Any ideas on how I fix this?

Thanks!

Answer 1

I followed suggestions from @ScottHoltzman and @JMichael, and changed my code to this:

Sub Calibrar()
'
' Calibrar Macro
' Macro gravada em 20/04/2012 por Lahidro
'
' Atalho do teclado: Ctrl+Shift+P
' Calibrar Macro
' Salva o Pedido de Agendamento da Calibração com a notação Processo+Ramal+hidrometro
'
' Atalho do teclado: Ctrl+Shift+P
'
    Dim PDFFile As String
    Dim XLSFile As String
    Dim processo As String
    Dim ramal As String
    Dim medidor As String
    Dim situacaoPedido As String

    Application.ScreenUpdating = False
    Set planilha = Workbooks("Mod.10.066_Agendamento de Ensaio de Medidor e Planilhas Associadas (revisão 2015.12.04).xls")
    processo = planilha.Worksheets("Agendamento").Range("W17").Value
    ramal = planilha.Worksheets("Agendamento").Range("W19").Value
    medidor = planilha.Worksheets("Agendamento").Range("E23").Value
    situacaoPedido = planilha.Worksheets("Agendamento").Range("E11").Value

    XLSFile = processo & "_" & ramal & "_" & medidor & "_" & "Agendamento" & ".xls"
    planilha.SaveAs Filename:="\\PMPA-FS3\DMAE-LabHid-Calibra-Agendamento$\Solicitações\" & processo & "_" & ramal & "_" & medidor & "_Agendamento" & ".xls"

    PDFFile = processo & "_" & ramal & "_" & medidor & "_" & situacaoPedido & "_" & "Pedido" & ".xls"

    planilha.Worksheets("Agendamento").Move
    ActiveWorkbook.SaveAs Filename:="\\PMPA-FS3\DMAE-LabHid-Calibra-Agendamento$\Pedidos\" & processo & "_" & ramal & "_" & medidor & "_" & situacaoPedido & "_" & "Pedido" & ".xls"
    Worksheets.PrintOut Preview:=False, Copies:=1, ActivePrinter:="CutePDF Writer on CPW2:", PrToFileName:="", Collate:=False

    Workbooks(XLSFile).Sheets("Controle").Move After:=Workbooks(PDFFile).Sheets(1)
    planilha.SaveAs Filename:="\\PMPA-FS3\DMAE-LabHid-Calibra-AnaliseCritica$\" & processo & "_" & ramal & "_" & medidor & "_Analise Critica" & ".xls"
End Sub

And it worked. Thank you very much!