H
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
"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