2003 macros/charts not working in 2007

M

monish

New Member
Joined
Aug 17, 2005
Messages
12
Hi - I have a workbook of macros written in 2003 version byut they keep hanging up in 2007 version...apparently in the Chart Type row withih VBA code. Hoping someone can help me here. Code (sorry it is so long) is pasted below.

Error received on line:

ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"

Error is noted as:

Run-time error '-2147467259 (80004005)': The specified diemnion is not valid for the current chart type.

==================================

Sub TableChart_Maker()
'
' Macro recorded 1/16/2008
'
Dim xx As Integer, yy As Integer, zz As Integer
Dim recdcount As Integer, predcount As Integer
Dim xwidth As Integer, xheight As Integer, Rw As Integer, cl As Integer
Dim sheetn As Integer

predcount = Range("TOTPREDTCONT").Value
recdcount = Range("TOTRECDCONT") + 3

sheetn = Sheets.count

' If the number of records is greater than 997, then the macro will stop
If recdcount > 4997 Then
Sheets("DATA").Select
Range("A5001").Select
MsgBox "Paste formulars in rows below 5000, and revise formulars in I to K", vbExclamation
End

Else
'if the number of records is less than 997, then the macro will product the tables and charts

If sheetn > 4 Then
MsgBox "Please delete all chart tabs!", vbExclamation
End
Else

' Chart size and position
xwidth = 580
xheight = 370
Rw = 5
cl = 5


zz = 0




For i = 4 To recdcount
Sheets("DATA").Select
'Extract data of each preditor from DATA sheet and then copy them to 'TEMPLATE' sheet
While Cells(i, "F").Value = "BL"
xx = Cells(i, "D").Value - 1
yy = Cells(i, "B").Value
If xx > 94 Then
Sheets("TEMPLATE").Select
Range("A1").Select
MsgBox "Paste formulars in rows below 100 and revise formulars in line 4", vbExclamation
End

Else
Cells(i, "F").Select
ActiveCell.Offset(-xx, 1).Activate
Range(ActiveCell, Cells(i, "J")).Select
Selection.Copy
Sheets("TEMPLATE").Select
Range("O5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(Cells(5, "B"), Cells(5 + xx, "G")).Select
Selection.Copy
Sheets("TABLE").Select

'Copy the table of the first preditor from TEMPLATE sheet to TABLE sheet.
If yy = 1 Then
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("TEMPLATE").Select
Range("B4", "G4").Select
Selection.Copy
Sheets("TABLE").Select
Cells(6 + xx, "A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'Add chart for the first predictor
myname = Cells(5, "A").Value
Application.ScreenUpdating = False
Worksheets.Add
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.count)
Application.ScreenUpdating = True ' Enables screen refreshing.
ActiveSheet.Name = myname

Sheets("TABLE").Select
Union(Range(Cells(5, "B"), Cells(5 + xx, "B")), Range(Cells(5, "F"), Cells(5 + xx, "F")), _
Range(Cells(5, "E"), Cells(5 + xx, "E"))).Select
myrange = Selection.Address
myrelodds = Range(Cells(5, "F"), Cells(5 + xx, "F")).Address
mycolumnrange = Range(Cells(5, "E"), Cells(5 + xx, "E")).Address
mysheetname = ActiveSheet.Name
Application.CutCopyMode = False
Charts.Add
mychart = ActiveChart.Name
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets(mysheetname).Range(myrange), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Values = Sheets("TABLE").Range(myrelodds)
ActiveChart.SeriesCollection(2).Values = Sheets("TABLE").Range(mycolumnrange)
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(1).Name = "=""Target Density"""
ActiveChart.SeriesCollection(2).Name = "=""Dist'n"""
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = myname
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Parameter"
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Dist'n"
End With

With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With


ActiveChart.Location Where:=xlLocationAsObject, Name:=myname

With ActiveSheet.ChartObjects(yy)
.Width = xwidth
.Height = xheight
.Left = cl
.Top = Rw
End With


Sheets("TEMPLATE").Select
Range("O5:R103").Select
Selection.ClearContents

'If it isn't the first predictor, then copy the standard table to TABLE sheet
Else
Cells(3 + zz + 2 * yy, "A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("TEMPLATE").Select
Range("B4", "G4").Select
Selection.Copy
Sheets("TABLE").Select
Cells(4 + zz + 2 * yy + xx, "A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'Add charts for the rest predictors
myname = Cells(3 + zz + 2 * yy, "A").Value
Application.ScreenUpdating = False
Worksheets.Add
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.count)
Application.ScreenUpdating = True ' Enables screen refreshing.
ActiveSheet.Name = myname

Sheets("TABLE").Select
Union(Range(Cells(3 + zz + 2 * yy, "B"), Cells(3 + zz + 2 * yy + xx, "B")), _
Range(Cells(3 + zz + 2 * yy, "F"), Cells(3 + zz + 2 * yy + xx, "F")), _
Range(Cells(3 + zz + 2 * yy, "E"), Cells(3 + zz + 2 * yy + xx, "E"))).Select
myrange = Selection.Address
myrelodds = Range(Cells(3 + zz + 2 * yy, "F"), Cells(3 + zz + 2 * yy + xx, "F")).Address
mycolumnrange = Range(Cells(3 + zz + 2 * yy, "E"), Cells(3 + zz + 2 * yy + xx, "E")).Address
mysheetname = ActiveSheet.Name
Application.CutCopyMode = False
Charts.Add
mychart = ActiveChart.Name
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets(mysheetname).Range(myrange), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Values = Sheets("TABLE").Range(myrelodds)
ActiveChart.SeriesCollection(2).Values = Sheets("TABLE").Range(mycolumnrange)
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(1).Name = "=""Target Density"""
ActiveChart.SeriesCollection(2).Name = "=""Dist'n"""
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = myname
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Parameter"
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Dist'n"
End With

With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With


ActiveChart.Location Where:=xlLocationAsObject, Name:=myname

With ActiveSheet.ChartObjects(1)
.Width = xwidth
.Height = xheight
.Left = cl
.Top = Rw
End With


Sheets("TEMPLATE").Select
Range("O5:R103").Select
Selection.ClearContents



End If
zz = zz + xx + 1
End If

Wend

Next i


Sheets("TEMPLATE").Select
Range("A1").Select
Sheets("TABLE").Select
Range("A1").Select
Sheets("DATA").Select
Range("G1").Select

Sheets("INSTRUCTION").Select
Cells(21, "G").Select
MsgBox ("Tables and Charts are done!")

End If
End If

End Sub

=====================

Thanks for taking time to read this,
M
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
Sorry all - just realized how to post code! Hope this is easier to read and accommodate.

Thanks again!

Code: 
Sub TableChart_Maker()
'
' Macro recorded 1/16/2008
'
Dim xx As Integer, yy As Integer, zz As Integer
Dim recdcount As Integer, predcount As Integer
Dim xwidth As Integer, xheight As Integer, Rw As Integer, cl As Integer
Dim sheetn As Integer

predcount = Range("TOTPREDTCONT").Value
recdcount = Range("TOTRECDCONT") + 3

sheetn = Sheets.count

' If the number of records is greater than 997, then the macro will stop
If recdcount > 4997 Then
Sheets("DATA").Select
Range("A5001").Select
MsgBox "Paste formulars in rows below 5000, and revise formulars in I to K", vbExclamation
End

Else
'if the number of records is less than 997, then the macro will product the tables and charts

If sheetn > 4 Then
MsgBox "Please delete all chart tabs!", vbExclamation
End
Else

' Chart size and position
xwidth = 580
xheight = 370
Rw = 5
cl = 5


zz = 0




For i = 4 To recdcount
Sheets("DATA").Select
'Extract data of each preditor from DATA sheet and then copy them to 'TEMPLATE' sheet
While Cells(i, "F").Value = "BL"
xx = Cells(i, "D").Value - 1
yy = Cells(i, "B").Value
If xx > 94 Then
Sheets("TEMPLATE").Select
Range("A1").Select
MsgBox "Paste formulars in rows below 100 and revise formulars in line 4", vbExclamation
End

Else
Cells(i, "F").Select
ActiveCell.Offset(-xx, 1).Activate
Range(ActiveCell, Cells(i, "J")).Select
Selection.Copy
Sheets("TEMPLATE").Select
Range("O5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range(Cells(5, "B"), Cells(5 + xx, "G")).Select
Selection.Copy
Sheets("TABLE").Select

'Copy the table of the first preditor from TEMPLATE sheet to TABLE sheet.
If yy = 1 Then
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("TEMPLATE").Select
Range("B4", "G4").Select
Selection.Copy
Sheets("TABLE").Select
Cells(6 + xx, "A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'Add chart for the first predictor
myname = Cells(5, "A").Value
Application.ScreenUpdating = False
Worksheets.Add
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.count)
Application.ScreenUpdating = True ' Enables screen refreshing.
ActiveSheet.Name = myname

Sheets("TABLE").Select
Union(Range(Cells(5, "B"), Cells(5 + xx, "B")), Range(Cells(5, "F"), Cells(5 + xx, "F")), _
Range(Cells(5, "E"), Cells(5 + xx, "E"))).Select
myrange = Selection.Address
myrelodds = Range(Cells(5, "F"), Cells(5 + xx, "F")).Address
mycolumnrange = Range(Cells(5, "E"), Cells(5 + xx, "E")).Address
mysheetname = ActiveSheet.Name
Application.CutCopyMode = False
Charts.Add
mychart = ActiveChart.Name
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets(mysheetname).Range(myrange), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Values = Sheets("TABLE").Range(myrelodds)
ActiveChart.SeriesCollection(2).Values = Sheets("TABLE").Range(mycolumnrange)
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(1).Name = "=""Target Density"""
ActiveChart.SeriesCollection(2).Name = "=""Dist'n"""
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = myname
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Parameter"
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Dist'n"
End With

With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With


ActiveChart.Location Where:=xlLocationAsObject, Name:=myname

With ActiveSheet.ChartObjects(yy)
.Width = xwidth
.Height = xheight
.Left = cl
.Top = Rw
End With


Sheets("TEMPLATE").Select
Range("O5:R103").Select
Selection.ClearContents

'If it isn't the first predictor, then copy the standard table to TABLE sheet
Else
Cells(3 + zz + 2 * yy, "A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("TEMPLATE").Select
Range("B4", "G4").Select
Selection.Copy
Sheets("TABLE").Select
Cells(4 + zz + 2 * yy + xx, "A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'Add charts for the rest predictors
myname = Cells(3 + zz + 2 * yy, "A").Value
Application.ScreenUpdating = False
Worksheets.Add
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.count)
Application.ScreenUpdating = True ' Enables screen refreshing.
ActiveSheet.Name = myname

Sheets("TABLE").Select
Union(Range(Cells(3 + zz + 2 * yy, "B"), Cells(3 + zz + 2 * yy + xx, "B")), _
Range(Cells(3 + zz + 2 * yy, "F"), Cells(3 + zz + 2 * yy + xx, "F")), _
Range(Cells(3 + zz + 2 * yy, "E"), Cells(3 + zz + 2 * yy + xx, "E"))).Select
myrange = Selection.Address
myrelodds = Range(Cells(3 + zz + 2 * yy, "F"), Cells(3 + zz + 2 * yy + xx, "F")).Address
mycolumnrange = Range(Cells(3 + zz + 2 * yy, "E"), Cells(3 + zz + 2 * yy + xx, "E")).Address
mysheetname = ActiveSheet.Name
Application.CutCopyMode = False
Charts.Add
mychart = ActiveChart.Name
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets(mysheetname).Range(myrange), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Values = Sheets("TABLE").Range(myrelodds)
ActiveChart.SeriesCollection(2).Values = Sheets("TABLE").Range(mycolumnrange)
ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(1).Name = "=""Target Density"""
ActiveChart.SeriesCollection(2).Name = "=""Dist'n"""
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = myname
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Parameter"
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Dist'n"
End With

With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = 1
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With


ActiveChart.Location Where:=xlLocationAsObject, Name:=myname

With ActiveSheet.ChartObjects(1)
.Width = xwidth
.Height = xheight
.Left = cl
.Top = Rw
End With


Sheets("TEMPLATE").Select
Range("O5:R103").Select
Selection.ClearContents



End If
zz = zz + xx + 1
End If

Wend

Next i


Sheets("TEMPLATE").Select
Range("A1").Select
Sheets("TABLE").Select
Range("A1").Select
Sheets("DATA").Select
Range("G1").Select

Sheets("INSTRUCTION").Select
Cells(21, "G").Select
MsgBox ("Tables and Charts are done!")

End If
End If

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Counter in For Each loop
Replies
2
Views
204
Possum17
P
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
118
willow1985
willow1985
C
  • Question
Run-time error -2147319767 (80028029) ... SOMETIMES
Replies
1
Views
87
footoo
F
R
  • Solved
How to loop a macro
Replies
2
Views
385
rlcurlee
R
L
  • Question
VBA ERROR: Cannot Paste the Selected Copied Content
Replies
7
Views
210
Joe4
Joe4

Forum statistics

Threads
1,215,219
Messages
6,123,685
Members
449,117
Latest member
Aaagu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top