Building a Macro for different size trend-Charts??

  • Thread starter Thread starter Anthony0247
  • Start date Start date
A

Anthony0247

Hello everyone, I need help. (who doesn't, lol)

I am trying to build a macro to
create real estate trend charts/graphs. I need to work with different
amounts
of data every time; however, the column layout is the same everytime.
I recorded a basic marco, but it messes up when I have different
amounts
of sales to graph. I am self taught and I need someone
to open my eyes a little further. Any help would be greatly
appreciated.


Anthony.
 
Anthony,

It would help if you posted your code.

But, generally, if you get code like

Range("A2:D10")

you can change that to

Range("A2:D" & Cells(Rows.Count,4).End(xlUp).Row)

OR

Range("A2").CurrentRegion (but this depends on layout with headers etc)

OR

Range("A2", Range("A2").End(xlDown).End(xlToRight))

The best solution depends on your layout.

HTH,
Bernie
MS Excel MVP
 
Bernie,

Thank you for your advice and help. Here is the code below. If I
change the size of the data set it won't finish the macro.

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 4/13/2008 by Anthony Young
'
' Keyboard Shortcut: Ctrl+a
'
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "Sales Volume"
Range("B2").Select
ActiveCell.FormulaR1C1 = "1/1/1900"
Range("B2").Select
Selection.NumberFormat = "0.00"
Selection.NumberFormat = "0.0"
Selection.NumberFormat = "0"
Selection.AutoFill Destination:=Range("B2:B193")
Range("B2:B193").Select
Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("C1").Select
ActiveCell.FormulaR1C1 = "1500-1600 SqFt Homes"
Range("D1").Select
ActiveCell.FormulaR1C1 = "1600-1700 SqFt Homes"
Columns("C:D").Select
Columns("C:D").EntireColumn.AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
ActiveWindow.SmallScroll Down:=105
Range("C110").Select
Range(Selection, Selection.End(xlDown)).Select
Range("C164").Select
ActiveWindow.SmallScroll Down:=-60
Range("C110").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut Destination:=Range("D110:D193")
Range("D110:D193").Select
ActiveWindow.SmallScroll Down:=-195
Columns("A:A").Select
Range("A1:FH193").Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
ActiveCell.FormulaR1C1 = "DATE"
Range("A1:D1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"Sheet1!R1C1:R193C4").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable3").PivotSelect "",
xlDataAndLabel, True
ActiveSheet.PivotTables("PivotTable3").Format xlReport10
With ActiveSheet.PivotTables("PivotTable3").PivotFields("DATE")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable3").AddDataField
ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("1500-1600 SqFt Homes"), _
"Count of 1500-1600 SqFt Homes", xlCount
ActiveSheet.PivotTables("PivotTable3").AddDataField
ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("1600-1700 SqFt Homes"), _
"Count of 1600-1700 SqFt Homes", xlCount
ActiveSheet.PivotTables("PivotTable3").AddDataField
ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Sales Volume"), "Sum of Sales
Volume", xlSum
Range("B3").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"Count of 1500-1600 SqFt Homes").Function = xlAverage
ActiveSheet.PivotTables("PivotTable3").PivotSelect _
"'Count of 1600-1700 SqFt Homes'", xlDataAndLabel, True
Range("C3").Select
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
"Count of 1600-1700 SqFt Homes").Function = xlAverage
Range("A3").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False,
False, False, _
False, False, True, True)
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Sheet4").Range("A3")
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.ChartArea.Select
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
"Lines on 2 Axes"
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).ChartType = xlColumnClustered
ActiveChart.Axes(xlValue, xlSecondary).Select
With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScaleIsAuto = True
.MaximumScale = 40
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Trendlines.Add(Type:=xlPolynomial,
Order:=4 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
False).Select
ActiveChart.SeriesCollection(3).Trendlines(1).Select
With Selection
.Type = xlPolynomial
.Order = 4
.Forward = 0
.Backward = 0
.InterceptIsAuto = True
.DisplayEquation = False
.DisplayRSquared = False
.Name = "Sales Volume Trend"
End With
ActiveChart.SeriesCollection(3).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
Selection.Fill.OneColorGradient Style:=msoGradientVertical,
Variant:=3, _
Degree:=0.809796292057679
With Selection
.Fill.Visible = True
.Fill.ForeColor.SchemeColor = 54
End With
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlPolynomial,
Order:=5 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
False).Select
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Trendlines(1).Select
With Selection
.Type = xlPolynomial
.Order = 2
.Forward = 0
.Backward = 0
.InterceptIsAuto = True
.DisplayEquation = False
.DisplayRSquared = False
.Name = "1500-1600 SqFt Trend"
End With
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Trendlines(1).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThick
.LineStyle = xlContinuous
End With
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Trendlines.Add(Type:=xlPolynomial,
Order:=2 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
False, Name:="1600-1700 SqFt Trend").Select
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(2).Trendlines(1).Select
With Selection.Border
.ColorIndex = 5
.Weight = xlThick
.LineStyle = xlContinuous
End With
ActiveChart.PlotArea.Select
ActiveChart.Legend.Select
ActiveChart.Legend.LegendEntries(2).Select
Selection.Delete
ActiveChart.Legend.Select
ActiveChart.Legend.LegendEntries(2).Select
Selection.Delete
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Sales Volume and Price Trend"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "DATE/
QUARTER"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales
Price"
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Sales
Volume"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlCategory).AxisTitle.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.Size = 18
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.ChartArea.Select
ActiveChart.ChartTitle.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.ChartArea.Select
End Sub
 
Bernie,

Thank you for your advice and help. Here is the code below. If I
change the size of the data set it won't finish the macro.

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 4/13/2008 by Anthony Young
'
' Keyboard Shortcut: Ctrl+a
'
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Sales Volume"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "1/1/1900"
    Range("B2").Select
    Selection.NumberFormat = "0.00"
    Selection.NumberFormat = "0.0"
    Selection.NumberFormat = "0"
    Selection.AutoFill Destination:=Range("B2:B193")
    Range("B2:B193").Select
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "1500-1600 SqFt Homes"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "1600-1700 SqFt Homes"
    Columns("C:D").Select
    Columns("C:D").EntireColumn.AutoFit
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    ActiveWindow.SmallScroll Down:=105
    Range("C110").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("C164").Select
    ActiveWindow.SmallScroll Down:=-60
    Range("C110").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut Destination:=Range("D110:D193")
    Range("D110:D193").Select
    ActiveWindow.SmallScroll Down:=-195
    Columns("A:A").Select
    Range("A1:FH193").Sort Key1:=Range("A1"), Order1:=xlAscending,
Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "DATE"
    Range("A1:D1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
        "Sheet1!R1C1:R193C4").CreatePivotTable TableDestination:="",
TableName:= _
        "PivotTable3", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable3").PivotSelect "",
xlDataAndLabel, True
    ActiveSheet.PivotTables("PivotTable3").Format xlReport10
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("DATE")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable3").AddDataField
ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("1500-1600 SqFt Homes"), _
        "Count of 1500-1600 SqFt Homes", xlCount
    ActiveSheet.PivotTables("PivotTable3").AddDataField
ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("1600-1700 SqFt Homes"), _
        "Count of 1600-1700 SqFt Homes", xlCount
    ActiveSheet.PivotTables("PivotTable3").AddDataField
ActiveSheet.PivotTables( _
        "PivotTable3").PivotFields("Sales Volume"), "Sum of Sales
Volume", xlSum
    Range("B3").Select
    ActiveSheet.PivotTables("PivotTable3").PivotFields( _
        "Count of 1500-1600 SqFt Homes").Function = xlAverage
    ActiveSheet.PivotTables("PivotTable3").PivotSelect _
        "'Count of 1600-1700 SqFt Homes'", xlDataAndLabel, True
    Range("C3").Select
    ActiveSheet.PivotTables("PivotTable3").PivotFields( _
        "Count of 1600-1700 SqFt Homes").Function = xlAverage
    Range("A3").Select
    Selection.Group Start:=True, End:=True, Periods:=Array(False,
False, False, _
        False, False, True, True)
    Charts.Add
    ActiveChart.SetSourceData Source:=Sheets("Sheet4").Range("A3")
    ActiveChart.Location Where:=xlLocationAsNewSheet
    ActiveChart.ChartArea.Select
    ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _
        "Lines on 2Axes"
    ActiveChart.PlotArea.Select
    With Selection.Border
        .ColorIndex = 16
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    With Selection.Interior
        .ColorIndex = 2
        .PatternColorIndex = 1
        .Pattern = xlSolid
    End With
    ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(3).ChartType = xlColumnClustered
    ActiveChart.Axes(xlValue, xlSecondary).Select
    With ActiveChart.Axes(xlValue, xlSecondary)
        .MinimumScaleIsAuto = True
        .MaximumScale = 40
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(3).Trendlines.Add(Type:=xlPolynomial,
Order:=4 _
        , Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
        False).Select
    ActiveChart.SeriesCollection(3).Trendlines(1).Select
    With Selection
        .Type = xlPolynomial
        .Order = 4
        .Forward = 0
        .Backward = 0
        .InterceptIsAuto = True
        .DisplayEquation = False
        .DisplayRSquared = False
        .Name = "Sales Volume Trend"
    End With
    ActiveChart.SeriesCollection(3).Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
    End With
    Selection.Shadow = False
    Selection.InvertIfNegative = False
    Selection.Fill.OneColorGradient Style:=msoGradientVertical,
Variant:=3, _
        Degree:=0.809796292057679
    With Selection
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = 54
    End With
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlPolynomial,
Order:=5 _
        , Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
        False).Select
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
    With Selection
        .Type = xlPolynomial
        .Order = 2
        .Forward = 0
        .Backward = 0
        .InterceptIsAuto = True
        .DisplayEquation = False
        .DisplayRSquared = False
        .Name = "1500-1600 SqFt Trend"
    End With
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).Trendlines(1).Select
    With Selection.Border
        .ColorIndex =3
        .Weight = xlThick
        .LineStyle = xlContinuous
    End With
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).Trendlines.Add(Type:=xlPolynomial,
Order:=2 _
        , Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
        False, Name:="1600-1700 SqFt Trend").Select
    ActiveChart.SeriesCollection(2).Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlAutomatic
        .MarkerForegroundColorIndex = xlAutomatic
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 5
        .Shadow = False
    End With
    ActiveChart.SeriesCollection(1).Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlNone
    End With
    With Selection
        .MarkerBackgroundColorIndex = xlAutomatic
        .MarkerForegroundColorIndex = xlAutomatic
        .MarkerStyle = xlNone
        .Smooth = False
        .MarkerSize = 5
        .Shadow = False
    End With
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(2).Trendlines(1).Select
    With Selection.Border
        .ColorIndex = 5
        .Weight = xlThick
        .LineStyle = xlContinuous
    End With
    ActiveChart.PlotArea.Select
    ActiveChart.Legend.Select
    ActiveChart.Legend.LegendEntries(2).Select
    Selection.Delete
    ActiveChart.Legend.Select
    ActiveChart.Legend.LegendEntries(2).Select
    Selection.Delete
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Sales Volume and Price Trend"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text ="DATE/
QUARTER"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales
Price"
        .Axes(xlValue, xlSecondary).HasTitle = True
        .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Sales
Volume"
    End With
    With ActiveChart.Axes(xlCategory)
        .HasMajorGridlines = False
        .HasMinorGridlines = False
    End With
    With ActiveChart.Axes(xlValue)
        .HasMajorGridlines = True
        .HasMinorGridlines = False
    End With
    ActiveChart.Axes(xlValue).AxisTitle.Select
    Selection.AutoScaleFont = False
    With Selection.Font
        .Name = "Arial"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    ActiveChart.Axes(xlCategory).AxisTitle.Select
    Selection.AutoScaleFont = False
    With Selection.Font
        .Name = "Arial"
        .Size = 20
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Select
    Selection.AutoScaleFont = False
    With Selection.Font
        .Name = "Arial"
        .Size = 18
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    ActiveChart.ChartArea.Select
    ActiveChart.ChartTitle.Select
    Selection.AutoScaleFont = False
    With Selection.Font
        .Name = "Arial"
        .Size = 20
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    ActiveChart.ChartArea.Select
End Sub
Click to expand...

Try EZplot for Excel to quickly update data for many plots based on
parameter names. A demo version is available at www.OfficeExpander.com
Cheers!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to change chart references 10
updating data location to build a VBA graph 2
Macro for creating a chart 1
Macro that names a sheet 3
Reposition Chart Macro 6
Need code for fill a number trend down 3
building a range for SetSourceData 7
macro on different reports CTRL+F in formula 2

Back
Top