Excel copy/paste values/comments/formats intermittent VBA failure

Question

I have a very large Excel file with 14k+ lines of code and 320+ sheets in it.

The file's VBA imports data from other Excel spreadsheets, does some calculations and "exports" the results.

The "export" involves opening a new workbook and copying across values, formats, comments and the colours of those sheets, then saving that new file.

As part of the code, I have written something that checks the data has been pasted in. Just a very simple:

If Range("A2").Value <> True Then
    'error has occured with export
    MsgBox "error", vbCritical, "AURORA Model: Error #MU-ID: Unable to Export Datafile"
    Exit Sub
End If

As I say, this error is intermittent and I can't seem to work out what causes it. I assume it's a memory thing. Any advice?

Below is the interesting part of the code:

Sub ExportData(filename As String, FileLocation As String, export_reason As String)

'
' this macro copys over the values and comments from the model
' this is run by clicking the "butExport" button on the userform
'

Dim sheets_all As Integer
Dim sheets_cycle As Integer
Dim sheet_name As String

Dim sheet_colour As Long

Dim wBook1 As String
Dim wBook2 As String
    'wBook1 is AuRORA
    wBook1 = ThisWorkbook.Name

    Application.SheetsInNewWorkbook = 1
    Workbooks.Add
    'wBook2 is what will be the new datafile
    wBook2 = ActiveWorkbook.Name

    'activate the Aurora Model
    Windows(wBook1).Activate
    'count the number of sheet in the model
    sheets_all = Sheets.Count

    Sheets("l_ERROR_IPT").ListObjects("tbl_ERROR_IPT").Range.AutoFilter Field:=3
    Sheets("l_MRUN").ListObjects("tbl_MRUN").Range.AutoFilter Field:=2, _
        Criteria1:=""

    On Error Resume Next
    For sheets_cycle = 1 To sheets_all

        sheet_name = Sheets(sheets_cycle).Name

        Application.StatusBar = _
            "Running Macro. Exporting data " & sheets_cycle _
            & " (" & sheet_name & ") of " & sheets_all & " (" & Round(sheets_cycle / sheets_all * 100, 1) & "%)"

        'if the cycling sheet name begins with s, i, n, l, u, c, o, p or d then...
        If Mid(sheet_name, 1, 2) = "i_" Or Mid(sheet_name, 1, 2) = "u_" _
            Or Mid(sheet_name, 1, 2) = "c_" Or Mid(sheet_name, 1, 2) = "o_" _
            Or Mid(sheet_name, 1, 2) = "p_" Or Mid(sheet_name, 1, 2) = "d_" Or _
            Mid(sheet_name, 1, 2) = "l_" Or Mid(sheet_name, 1, 2) = "s_" Or _
            Mid(sheet_name, 1, 2) = "n_" Then
                Sheets(sheets_cycle).Select
                'if it's not a "p_" sheet then show all data
                If Mid(sheet_name, 1, 2) <> "p_" Then ActiveSheet.ShowAllData
                'copy all cells
                Range("A1:AZ2000").Copy
                sheet_colour = Sheets(sheets_cycle).Tab.Color
                'activate datafile
                Windows(wBook2).Activate
                'paste values and comments
                With Range("A1")
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteComments
                    .PasteSpecial xlPasteFormats
                End With
                'rename sheet with newly pasted data in datafile
                ActiveSheet.Name = sheet_name
                ActiveSheet.Tab.Color = sheet_colour
                'check that cell "A2" = "TRUE" otherwise there has been an error with the export
                If Range("A2").Value <> True Then
                    'error has occured with export
                    MsgBox "error", vbCritical, "AURORA Model: Error #MU-ID: Unable to Export Datafile"
                    Windows(wBook1).Activate
                    Exit Sub
                End If
                'add another sheet
                Sheets.Add After:=Sheets(Sheets.Count)
                Windows(wBook1).Activate
        End If
    Next sheets_cycle

    'Delete the last sheet and select the front sheet (d_file_info_sheet)
    Windows(wBook2).Activate
    Sheets(Sheets.Count).Delete
    Sheets(1).Select

    'save datafile with a random password
    ActiveWorkbook.SaveAs filename:=FileLocation & filename, FileFormat:=50, WriteResPassword:= _
        funcRandNumbersLarge, ReadOnlyRecommended:=True

    ActiveWorkbook.Close
    Sheets(1).Select

    MsgBox "Datafile exported and saved as """ & ReconciliationModel.labExportFile.Caption _
        & """. Please check that this has been saved correctly before closing the exported datafile (which has been left open).", vbOKOnly, "AURORA Model: Datafile exported"

I'm starting to tear my hair out over this issue so any advice or suggestions would be very welcome!

Thanks

Tom

Try changing your sheets_cycle loop to a `For Each` style loop instead. — SierraOscar, Feb 17 '15 at 09:05
What's the actual error you are getting? Which line throw it? And what are you trying to achieve with this `If Range("A2").Value <> True Then`? — , Feb 17 '15 at 09:17
Hi, If I get rid of the line "On Error Resume Next" then the error is thrown up on ".PasteSpecial xlPasteValues". The message I get is "Run Time Error '1004' PasteSpecial method of Range class failed". The thing is, if I then move the 'yellow execute line' thing so that it re-copies then attempts pasting again (stepping through it with F8) it works fine. I continue to run it and then it fails again a few sheets later. As I said before, it's intermittent - the error isn't always on the same sheet. the purpose of the "If Range("A2").Value <> True Then" is to catch those failed pastes. — tom_j_uk, Feb 24 '15 at 09:42
Hi @SO. Unfortunately, the `For Each` loop isn't great as I have sheets with up to 50 columns and rows of up to 1500 lines so doing a `For Each cell` on 75,000 cells across 300+ sheets for values, comments and formats isn't the quickest! — tom_j_uk, Mar 16 '15 at 12:44
Sorry. Yes, I'll give that a go. I seemed to only read the `For Each` from your post but ignored the "sheets_cycle". What I ended up doing was replacing the copy/paste section with a `For Each cell_cycle In transfer_range` and `Workbooks(wBook2).Sheets(paste_sheet_cycle + 1).Range(cell_cycle.Address).Value = cell_cycle.Value` etc. Anyway, I'll add it now and see if that helps. :) — tom_j_uk, Mar 17 '15 at 13:02

score 1 · Answer 1 · answered Oct 20 '15 at 02:30

1

Since you are copying large amount of data, you need to clear the memory. after you pasting the values.

Please use the below code at the end of pastespecial line

Application.cutcopymode = False
Application.cutcopymode = True

Try to use "Activesheet.Range" instead of Range in your code

answered Oct 20 '15 at 02:30

PASUMPON V N

1,186
2
10
17

Thank you very much, @pasumpon-v-n; `Application.cutcopymode = False` resolved an intermittent "PasteSpecial method of Range class failed" error when pasting rows. It appears that the `Application.cutcopymode = True` line is not necessary in practice (or theory: https://stackoverflow.com/a/33833319/131415 ). – Miles Wolbe Oct 28 '21 at 23:37

Excel copy/paste values/comments/formats intermittent VBA failure

1 Answers1