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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,214,779
Messages
6,121,509
Members
449,036
Latest member
dudeinaghillie

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