Macro throwing out 'memory' error

[krish2503]

Hi, I am having 'memory' error issue whilst running a file and wonder if anyone can help tweak the memory to improve the file performance. I am using Excel 2010 version. Thanks in advance.

I have a master file with numerous country tabs as well as reporting tabs which pull data from the country tabs. Initially I had a macro which activated an essbase retrieve function and populated each of the country tabs. As the number of rows increased (close to 4800 rows and columns extednign to 'DR' now), running this macro has become next to impossible. So I have now gone for having a separate file which just refreshes the essbase retrieves (and this works fine). I have set up a macro in the master file which then opens this source file and copypastes values from each of the tabs into the corresponding country tab in the master file. But this throws up a 'Excel cannot complete this task with available resources. Choose less data or close other applications’…this despite me having closed all other applications. File size of master file is 13MB and that of the source file (where the retrieve is done) is 18MB. So, effectively when I do the transfer, I have both files open.

I am attaching the code below. I plan to tweak the code to include a 'Do While' to pull all country data. I started initially but trying to pull in two sheets data only and this is crashing...so obvoulsy trying to run this macro for 12 countries is not going to work!!

Sub CopyPaste ()
'
' Copy data from Source worksheet to master workbook
'
    Dim wb1 As Workbook
        
    FileToOpen = Application.GetOpenFilename _
     (Title:="Please choose a Report to Copy from", _
      FileFilter:="Excel Files,*.xls*")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wb1 = Workbooks.Open(Filename:=FileToOpen)
    wb1.Sheets("Revenue").UsedRange.Copy
    ThisWorkbook.Sheets("Revenue").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    wb1.Sheets("France").UsedRange.Copy
    ThisWorkbook.Sheets("France").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    End If

    wb1.Close SaveChanges:=False
    
End Sub

[krish2503]

apologies..I meant ' tweak the macro'!!

[xlnitwit]

Hi,

About the only change I can see that you could make there would be to add

Application.CutCopyMode = False

after each paste

[BellyGas]

Firstly, read this: https://www.mrexcel.com/forum/excel-...clipboard.html

Then google other ways of copying excel data without putting many megabytes on the clipboard.

Then google how much upgrading to 64bit CPU + O/S + Office is and consider converting to that, cos 64bit removes the memory restriction. Well, I say removes.....Excel 32bit has a a 2Gb memory maximum....no matter how much RAM you've got, Excel will only use 2Gb of it. 64bit Excel has an 8Tb limit......ie 8000Gb.

[xlnitwit]

Excel 32bit has a a 2Gb memory maximum....no matter how much RAM you've got, Excel will only use 2Gb of it.

That depends on which version you have, and whether your OS is 32 or 64bit- https://support.microsoft.com/en-gb/...ange-for-excel

[BellyGas]

That depends on which version you have, and whether your OS is 32 or 64bit- https://support.microsoft.com/en-gb/...ange-for-excel

That's new, didn't know that. Ta.

[Daminc2]

I'm new back here but I think I remember advice that included these bits of code that might be worth looking into:

Application.ScreenUpdating = False 'To Turn Off at the start of code.
Application.ScreenUpdating = True 'To Turn on at the end of the code.

and

Application.Calculation = xlCalculationManual 'To turn off the automatic calculation
Application.Calculation = xlCalculationAutomatic 'To turn On the automatic calculation

That might help until an expert comes along

[krish2503]

Hi, thanks for the help. I have tried plugging in the suggestions given by xlnitwit and Daminc2 but now it seems to be throwing up a 'Run time error '1004': Method 'Open' of object 'Workbooks' failed' and when I click ok, it says 'Can't execte code in break mode'. On clicking debug, it highlights the 'Set wb1 = Workbooks.Open (Filename:=FileToOpen)' code. Could anyone help? Is it still caused by the memory issue?

Sub Copypaste ()
'
' Copy data from region2 worksheet to master workbook
'
    Dim wb1 As Workbook
        
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    
    FileToOpen = Application.GetOpenFilename _
     (Title:="Please choose a Report to Copy from", _
      FileFilter:="Excel Files,*.xls*")

If FileToOpen = False Then
    MsgBox "No File Specified.", vbExclamation, "ERROR"
    Exit Sub
Else
    Set wb1 = Workbooks.Open(Filename:=FileToOpen)
    wb1.Sheets("Revenue").UsedRange.Copy
    ThisWorkbook.Sheets("Revenue").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False
    wb1.Sheets("France").UsedRange.Copy
    ThisWorkbook.Sheets("France").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False

End If

    wb1.Close SaveChanges:=False
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

BellyGas..thanks for highlighting the systems/OS issues...since this is a company laptop and network, not sure if I can get much help there...and soon:-(

[Daminc2]

Just a quick thought: Shouldn't the FileFilter include .xlsm? since the workbook is using Macro's?

[thatandyward]

As you're only copying the values and number formats I would avoid using copy/paste all together.

See if the below works (untested)

Sub CopyValuesAndNumberFormats()
'
' Copy data from Source worksheet to master workbook
'
    Dim wb1 As Workbook, wb1Data As Range
        
    'User selects Workbook to import from
    FileToOpen = Application.GetOpenFilename _
     (Title:="Please choose a Report to Copy from", _
      FileFilter:="Excel Files,*.xls*")

    'Exit Sub if no file selected
    If FileToOpen = False Then
        MsgBox "No File Specified.", vbExclamation, "ERROR"
        Exit Sub
    End If

    'Define import workbook & UsedRange
    Set wb1 = Workbooks.Open(Filename:=FileToOpen)
    Set wb1Data = wb1.Sheets("Revenue").UsedRange
    
    With ThisWorkbook.Sheets("Revenue")
    
        'copy values
        .Range(wb1Data.Address).Value2 = wb1Data.Value2
        
        'copy number formats
        .Range(wb1Data.Address).NumberFormat = wb1Data.NumberFormat
    
    End With

    'Close import Workbook
    wb1.Close SaveChanges:=False
    
End Sub

[xlnitwit]

@thatandyward

That would only work if all the cells have the same number format. Otherwise, the Numberformat will return Null, not an array of formats.

[thatandyward]

Ah. Good to know, wasn't aware of that.

Thanks for pointing out

[krish2503]

Thanks all...apologies for the delayed response. Had to sift thru all the data and remove as much stuff as required...not ideal as the lost information should now be collected by other means but at least have got the file to work now.