ListObjects - VBA code that worked in previous versions of Excel doesnot work in Excel 2013

[hdf]

I've looked around to see if I could find other people having a similar problem, but I have not been successful in finding other threads with the sameissue, so sorry if this has already been covered. Here's my problem, I have a macro that works fine in Excel 2007 and 2010, but crashes in 2013. I get a:

"Run-time error 1004 - The worksheet range for the table data must be on the same sheet as the table being created." message when I try to run it in Excel 2013."

No such error occurs in other versions of Excel and the code executes correctly. Below is the code up to the point where it crashes:

Can anybody provide some insight as to what might have changed in 2013 thatmakes this happen - and more importantly, what are possible solutions?

Thanks for any help.

-------------------------------------------

Sub Create_RandNum_IN_Table()
'
' Create_RandNum_Table Macro
' Creates Excel table where scenario RandNum will be placed
'

Dim a As Integer
Dim i As Integer
Dim x As Integer
Dim ClearRng As Range
Dim ClearTestFx As Range
Dim sh As Worksheet

Application.ScreenUpdating = False

For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect
Next sh

'Code clears the table of correlated random variables used in test formulae

x = Range("MC_Correlation_Clusters").Value
If x > 0 Then
On Error Resume Next
Range("TestCorrel[#All]").Select
Selection.ClearContents
End If


'Clear Distribution drop down cells and formulas at top of table (MIN, MAX,etc.)

Range("MC_Distribution_Types_RESET").Clear 'this range is 1000 columns long
Range("MC_Erase_RandNum_IN_Formulae").Clear 'this range is 1000 columns long

'code clears entire Correlation Inputs WS inputs and tables

Set ClearRng = Range(Range("MC_Cluster_Grid_START").Offset(0, 1), _
Range("MC_Cluster_Grid_START").SpecialCells(xlLastCell))

ClearRng.Clear

Set ClearTestFx = Range("MC_Dynamic_Test_Formulae") 'clears the test distribution formulas (clears 1000 columns)

ClearTestFx.Clear

'Code clears the previous RandNum table that was there
With Range("RandNum_IN[#All]")
.EntireRow.Delete
End With


Calculate

'Code creates the new RandNum table based on the dynamic range "MC_Dynamic_RandNum_IN"

ActiveSheet.ListObjects.Add(xlSrcRange, Range("MC_Dynamic_RandNum_IN"),, xlYes).Name = _
"RandNum_IN" <<<<<<CRASH SITE

....

End Sub

 

[Desmond Walsh]

I had a similar problem with code that worked perfectly for Excel 10 under XP but gave Run-time 1004 error when I ran the same code under Windows 7.

My code read in data from external databases (actually DBASEIV) into excel database tables. After examining the code closely I noticed that I clearedall the cells in my destination worksheet and then deleted the old copy ofthe database table on that sheet. The error disappeared when I deleted the table first and then cleared the cells. I suspect that I was calling a delete method on a table object that no longer existed resulting in the 1004 error. For some reason, Excel under XP tolerated this sloppy coding.

So, my advice is to check your code carefully. Error 1004 is a generic catch all type of error that gives no information. Just be aware that some aspect of sloppy coding might have been tolerated in your previous Excel environment but the new environment has decided to be less tolerant !.

 

[GS]

I had a similar problem with code that worked perfectly for Excel 10

under XP but gave Run-time 1004 error when I ran the same code under
Windows 7.

My code read in data from external databases (actually DBASEIV) into
excel database tables. After examining the code closely I noticed
that I cleared all the cells in my destination worksheet and then
deleted the old copy of the database table on that sheet. The error
disappeared when I deleted the table first and then cleared the
cells. I suspect that I was calling a delete method on a table
object that no longer existed resulting in the 1004 error. For some
reason, Excel under XP tolerated this sloppy coding.

So, my advice is to check your code carefully. Error 1004 is a
generic catch all type of error that gives no information. Just be
aware that some aspect of sloppy coding might have been tolerated in
your previous Excel environment but the new environment has decided
to be less tolerant !.

I found similar behavior with stuff that worked fine on XP/Vista/Win7
but bombed on Win8.1! I also found this behavior running on Win7HP and
Win7Pro, where HP allowed things Pro didn't.

It might be worth noting that as of v2010 all MS apps use VBA7 so it
handles 64-bit properly. I suspect the language got a thorough overhaul
for this purpose, but it (the language) essentially 'just works' in
both x32/x64 versions of apps. Note that x64 VBA projects must be
compiled in x64 versions, meaning you now need to maintain both for
now!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion