Excel – Run-Time error ‘32809’: Application-defined or object-defined error

excelexcel-2013vba

All,

I just upgraded to Excel 2013 and am running into a strange issue. I have a macro-enabled workbook that has worked successfully for quite some time now. I was updating some of the code and came across this error (32809) when trying to write to a specific sheet. In troubleshooting, I tried this.

Sheets("Summary").Range("G8").Value = "Test"

This resulted in the same error. Then I tried this.

Debug.Print ActiveSheet.Name

Same error. So I selected another sheet on the workbook and debug.print on the name. Worked fine.

Then I tried this.

Debug.Print Sheets(2).Name (This is the sheet number of the problemic sheet)

Same error. It seems to me that there is some form of corruption with this sheet but I am reticent about deleting and recreating. Any suggestions?

Thanks!

Best Answer

While it wasn't a perfect solution, here is how I finally resolved it. I had a user that was still on Excel 2010 make a copy the corrupt sheet within the same workbook, delete the corrupt sheet, then rename the new sheet to the original name. I was then able to use the workbook in Excel 2013 without issue.

Related Solutions

Excel – VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range

Perhaps your code is behind Sheet1, so when you change the focus to Sheet2 the objects cannot be found? If that's the case, simply specifying your target worksheet might help:

Sheets("Sheet1").Range("C21").Select

I'm not very familiar with how Select works because I try to avoid it as much as possible :-). You can define and manipulate ranges without selecting them. Also it's a good idea to be explicit about everything you reference. That way, you don't lose track if you go from one sheet or workbook to another. Try this:

Option Explicit

Sub CopySheet1_to_PasteSheet2()

    Dim CLastFundRow As Integer
    Dim CFirstBlankRow As Integer
    Dim wksSource As Worksheet, wksDest As Worksheet
    Dim rngStart As Range, rngSource As Range, rngDest As Range

    Set wksSource = ActiveWorkbook.Sheets("Sheet1")
    Set wksDest = ActiveWorkbook.Sheets("Sheet2")

    'Finds last row of content
    CLastFundRow = wksSource.Range("C21").End(xlDown).Row
    'Finds first row without content
    CFirstBlankRow = CLastFundRow + 1

    'Copy Data
    Set rngSource = wksSource.Range("A2:C" & CLastFundRow)

    'Paste Data Values
    Set rngDest = wksDest.Range("A21")
    rngSource.Copy
    rngDest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    'Bring back to top of sheet for consistancy
    wksDest.Range("A1").Select

End Sub

Best Answer

Related Solutions

Excel – VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range

Related Topic