Macro Problem with Printing Graphs

dacookmgnt

New Member
Joined
Feb 3, 2004
Messages
14
The following code is intended to run through a list, update a graph, print it and do it over again. It goes through once, prints the graph, and then gives me a Run-Time Error '91': Object variable or With block variable not set.

Sub List()
For I = 0 To 5

ActiveCell.Offset(1, 0).Range("A1").Select
ActiveWorkbook.Names.Add Name:="store", RefersToR1C1:=ActiveCell
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Next
End Sub

Please help, Gurus! I'm sure you can tell I am clueless.
Thanks,
Dave
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

MartinK

Active Member
Joined
Oct 30, 2003
Messages
384
It errors out because you have selected a ChartArea and on the beginning of the next loop you use ActiveCell.
No cell is active and that's why you get an error. Why do you continue to name the range? What should your macro do?
Martin
 

dacookmgnt

New Member
Joined
Feb 3, 2004
Messages
14
I would like the macro to print the chart and then go back to the list, look at the next number, update the graph, print and then go back to the list.
I am trying to loop through a list of numbers.
Thanks,
Dave
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
What list? Where's is it?

How does the chart get updated?

dacookmgnt said:
I would like the macro to print the chart and then go back to the list, look at the next number, update the graph, print and then go back to the list.
I am trying to loop through a list of numbers.
Thanks,
Dave
 

dacookmgnt

New Member
Joined
Feb 3, 2004
Messages
14
Book3
ABCDEFGH
1storeaprmaystoreaprmay
220110302011030
32022040
42032550
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

I can't seem to get the chart to copy using HTML Maker, but imagine that it is there right under the data set. I get the '91' error I mentioned above.
Any help is greatly appreciated.
Thanks,
Dvae
 

MartinK

Active Member
Joined
Oct 30, 2003
Messages
384
Hello,
I am still not quite sure what you want to achieve.
In case you want to loop through your rows and display Apr/may for different stores, try the following:
Code:
Sub PrintCharts()

    Set myChart = ActiveSheet.ChartObjects("Chart 1").Chart
    Set Titles = Range("B1:C1")
    For r = 2 To [a65536].End(3).Row
    
    With myChart
        Set data = Range(Cells(r, 2), Cells(r, 3))
        Set SourceData = Union(Titles, data)
        SourceData.Select
        .SetSourceData Source:=SourceData, PlotBy:=xlRows
        .ChartTitle.Text = "Store: " & Cells(r, 1)
        .PrintOut Copies:=1
    End With
    Next r

End Sub

I assume your data is in A1:C4. B and C are plotted, A is taken as a chart title.

martin
 

Watch MrExcel Video

Forum statistics

Threads
1,122,670
Messages
5,597,480
Members
414,143
Latest member
lonnie451

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
Top