Hi,

I have a worksheet which contains a number of Profit and Loss statements.
Each one is of EXACTLY the same format (ie: same number of rows, account
names, columns etc.

Below the first profit and loss statement are 3 charts, each of which uses
data from the statement above it to generate the lines etc.

What I now want to do is programatically insert the same three charts below
each of the subsequent profit and loss statements - the only change being the
different data sources and perhaps the titles.

I would do it manually however there are in excess of 75 profit and losses
at the moment, and there is also the likelihood that the number will increase
(dramatically). Given that each one is inserted using VBA, it seems logical
to also insert the charts using VBA, using the original three as the starting
points.

I have no problem finding the insertion points, or even inserting a chart,
but I am really struggling with:
1) copying and pasting the existing chart into a new location on the same
sheet; and
2) changing the data source to a new range

Can anyone help?

Hi
Would it make more sense to have your profit/loss data on one sheet and
bring that to your 3 charts? i.e. you would have ONE set of charts. Now
your problem is to efficiently bring the particular profit/loss info to
the data area for your charts so that you can see it. This might
involve creating a Userform that lets the user select the data they
need - once selected it might simply be copied to your data area for
the charts (so that the charts are updated). This should be easy to do,
so post back if you want some detail.
Doing things your way might well end up creating an enormous number of
graphic objects in your workbook, which will will slow it down horribly
as time goes on.
regards
Paul

rael_lucid wrote:
> Hi,
>
> I have a worksheet which contains a number of Profit and Loss statements.
> Each one is of EXACTLY the same format (ie: same number of rows, account
> names, columns etc.
>
> Below the first profit and loss statement are 3 charts, each of which uses
> data from the statement above it to generate the lines etc.
>
> What I now want to do is programatically insert the same three charts below
> each of the subsequent profit and loss statements - the only change being the
> different data sources and perhaps the titles.
>
> I would do it manually however there are in excess of 75 profit and losses
> at the moment, and there is also the likelihood that the number will increase
> (dramatically). Given that each one is inserted using VBA, it seems logical
> to also insert the charts using VBA, using the original three as the starting
> points.
>
> I have no problem finding the insertion points, or even inserting a chart,
> but I am really struggling with:
> 1) copying and pasting the existing chart into a new location on the same
> sheet; and
> 2) changing the data source to a new range
>
> Can anyone help?

Paul,

You are so right. I realised this whilst developing the solution for my
client. I was basically giving them exactly what they required, without
suggesting improvements. They are a public company and were looking for a way
of simplifying the printing of their financial statements on a monthly basis.
It was taking them in excess of 2 weeks to prepare and print the reports. I
have just returned from a meeting with them where I have offered (and they
accepted) the following solution:

Data pulled from existing databases into common MS Access DB.
MS Access report to print profit and loss statements
MS Excel Pivot Table to analyse reports.

I think the initial mistake was trying to build a single solution that:
1) was easy to print all reports
2) was easy analyse and modify single reports

Your advice only confirmed the decision.

Thanks

Rael

"(E-Mail Removed)" wrote:

> Hi
> Would it make more sense to have your profit/loss data on one sheet and
> bring that to your 3 charts? i.e. you would have ONE set of charts. Now
> your problem is to efficiently bring the particular profit/loss info to
> the data area for your charts so that you can see it. This might
> involve creating a Userform that lets the user select the data they
> need - once selected it might simply be copied to your data area for
> the charts (so that the charts are updated). This should be easy to do,
> so post back if you want some detail.
> Doing things your way might well end up creating an enormous number of
> graphic objects in your workbook, which will will slow it down horribly
> as time goes on.
> regards
> Paul
>
> rael_lucid wrote:
> > Hi,
> >
> > I have a worksheet which contains a number of Profit and Loss statements.
> > Each one is of EXACTLY the same format (ie: same number of rows, account
> > names, columns etc.
> >
> > Below the first profit and loss statement are 3 charts, each of which uses
> > data from the statement above it to generate the lines etc.
> >
> > What I now want to do is programatically insert the same three charts below
> > each of the subsequent profit and loss statements - the only change being the
> > different data sources and perhaps the titles.
> >
> > I would do it manually however there are in excess of 75 profit and losses
> > at the moment, and there is also the likelihood that the number will increase
> > (dramatically). Given that each one is inserted using VBA, it seems logical
> > to also insert the charts using VBA, using the original three as the starting
> > points.
> >
> > I have no problem finding the insertion points, or even inserting a chart,
> > but I am really struggling with:
> > 1) copying and pasting the existing chart into a new location on the same
> > sheet; and
> > 2) changing the data source to a new range
> >
> > Can anyone help?
>
>

Paul,

further to this, I have now completed the application. It is all controlled
from a single combobox and is working EXTREMELY well. The only problem I am
having is that the chart I have selected (3D pie) is sometimes overlapping
the data labels. Not a big deal, and the client is going to have to deal with
it.

My real issue is (and I have created a separate post on this forum, but you
might be able to assist straight away) I now want to add a button so that the
user can "Print All". ie: I need the code to effectively select each value
from the combobox, one at a time, and print out the page. Is this possible?
Am I going to crash the computer/printer if the list is over 100 items long?
Is there a better way?

Rael

"rael_lucid" wrote:

> Paul,
>
> You are so right. I realised this whilst developing the solution for my
> client. I was basically giving them exactly what they required, without
> suggesting improvements. They are a public company and were looking for a way
> of simplifying the printing of their financial statements on a monthly basis.
> It was taking them in excess of 2 weeks to prepare and print the reports. I
> have just returned from a meeting with them where I have offered (and they
> accepted) the following solution:
>
> Data pulled from existing databases into common MS Access DB.
> MS Access report to print profit and loss statements
> MS Excel Pivot Table to analyse reports.
>
> I think the initial mistake was trying to build a single solution that:
> 1) was easy to print all reports
> 2) was easy analyse and modify single reports
>
> Your advice only confirmed the decision.
>
> Thanks
>
> Rael
>
> "(E-Mail Removed)" wrote:
>
> > Hi
> > Would it make more sense to have your profit/loss data on one sheet and
> > bring that to your 3 charts? i.e. you would have ONE set of charts. Now
> > your problem is to efficiently bring the particular profit/loss info to
> > the data area for your charts so that you can see it. This might
> > involve creating a Userform that lets the user select the data they
> > need - once selected it might simply be copied to your data area for
> > the charts (so that the charts are updated). This should be easy to do,
> > so post back if you want some detail.
> > Doing things your way might well end up creating an enormous number of
> > graphic objects in your workbook, which will will slow it down horribly
> > as time goes on.
> > regards
> > Paul
> >
> > rael_lucid wrote:
> > > Hi,
> > >
> > > I have a worksheet which contains a number of Profit and Loss statements.
> > > Each one is of EXACTLY the same format (ie: same number of rows, account
> > > names, columns etc.
> > >
> > > Below the first profit and loss statement are 3 charts, each of which uses
> > > data from the statement above it to generate the lines etc.
> > >
> > > What I now want to do is programatically insert the same three charts below
> > > each of the subsequent profit and loss statements - the only change being the
> > > different data sources and perhaps the titles.
> > >
> > > I would do it manually however there are in excess of 75 profit and losses
> > > at the moment, and there is also the likelihood that the number will increase
> > > (dramatically). Given that each one is inserted using VBA, it seems logical
> > > to also insert the charts using VBA, using the original three as the starting
> > > points.
> > >
> > > I have no problem finding the insertion points, or even inserting a chart,
> > > but I am really struggling with:
> > > 1) copying and pasting the existing chart into a new location on the same
> > > sheet; and
> > > 2) changing the data source to a new range
> > >
> > > Can anyone help?
> >
> >

Hi
Something like this code/pseudocode should do it (untested)

Sub Print_All()
Dim i As Integer
Application.ScreenUpdating = False
'Create a hidden version of the Form
Userform1.Hide
With Userform1
'Select each list item in turn and make the output for that item
For i = 0 To .ViewList.ListCount - 1 'ViewList is your
combobox
.ViewList.Selected(i) = True
If i > 0 Then .ViewList.Selected(i - 1) = False
'Call sub to produce your output to print
Application.DisplayAlerts = False
ActiveSheet.PrintOut
Application.DisplayAlerts = True
Next i
End With
Unload Userform1
End Sub

I don't know about the print buffer issue - depends on the printer
memory whether it crashes or not?
regards
Paul

rael_lucid wrote:
> Paul,
>
> further to this, I have now completed the application. It is all controlled
> from a single combobox and is working EXTREMELY well. The only problem I am
> having is that the chart I have selected (3D pie) is sometimes overlapping
> the data labels. Not a big deal, and the client is going to have to deal with
> it.
>
> My real issue is (and I have created a separate post on this forum, but you
> might be able to assist straight away) I now want to add a button so that the
> user can "Print All". ie: I need the code to effectively select each value
> from the combobox, one at a time, and print out the page. Is this possible?
> Am I going to crash the computer/printer if the list is over 100 items long?
> Is there a better way?
>
> Rael
>
> "rael_lucid" wrote:
>
> > Paul,
> >
> > You are so right. I realised this whilst developing the solution for my
> > client. I was basically giving them exactly what they required, without
> > suggesting improvements. They are a public company and were looking for a way
> > of simplifying the printing of their financial statements on a monthly basis.
> > It was taking them in excess of 2 weeks to prepare and print the reports. I
> > have just returned from a meeting with them where I have offered (and they
> > accepted) the following solution:
> >
> > Data pulled from existing databases into common MS Access DB.
> > MS Access report to print profit and loss statements
> > MS Excel Pivot Table to analyse reports.
> >
> > I think the initial mistake was trying to build a single solution that:
> > 1) was easy to print all reports
> > 2) was easy analyse and modify single reports
> >
> > Your advice only confirmed the decision.
> >
> > Thanks
> >
> > Rael
> >
> > "(E-Mail Removed)" wrote:
> >
> > > Hi
> > > Would it make more sense to have your profit/loss data on one sheet and
> > > bring that to your 3 charts? i.e. you would have ONE set of charts. Now
> > > your problem is to efficiently bring the particular profit/loss info to
> > > the data area for your charts so that you can see it. This might
> > > involve creating a Userform that lets the user select the data they
> > > need - once selected it might simply be copied to your data area for
> > > the charts (so that the charts are updated). This should be easy to do,
> > > so post back if you want some detail.
> > > Doing things your way might well end up creating an enormous number of
> > > graphic objects in your workbook, which will will slow it down horribly
> > > as time goes on.
> > > regards
> > > Paul
> > >
> > > rael_lucid wrote:
> > > > Hi,
> > > >
> > > > I have a worksheet which contains a number of Profit and Loss statements.
> > > > Each one is of EXACTLY the same format (ie: same number of rows, account
> > > > names, columns etc.
> > > >
> > > > Below the first profit and loss statement are 3 charts, each of which uses
> > > > data from the statement above it to generate the lines etc.
> > > >
> > > > What I now want to do is programatically insert the same three charts below
> > > > each of the subsequent profit and loss statements - the only change being the
> > > > different data sources and perhaps the titles.
> > > >
> > > > I would do it manually however there are in excess of 75 profit and losses
> > > > at the moment, and there is also the likelihood that the number will increase
> > > > (dramatically). Given that each one is inserted using VBA, it seems logical
> > > > to also insert the charts using VBA, using the original three as the starting
> > > > points.
> > > >
> > > > I have no problem finding the insertion points, or even inserting a chart,
> > > > but I am really struggling with:
> > > > 1) copying and pasting the existing chart into a new location on the same
> > > > sheet; and
> > > > 2) changing the data source to a new range
> > > >
> > > > Can anyone help?
> > >
> > >

Hi
Forgot to say that this sub would be called by the button on Userform1.
regards
Paul

paul.robin...@it-tallaght.ie wrote:
> Hi
> Something like this code/pseudocode should do it (untested)
>
> Sub Print_All()
> Dim i As Integer
> Application.ScreenUpdating = False
> 'Create a hidden version of the Form
> Userform1.Hide
> With Userform1
> 'Select each list item in turn and make the output for that item
> For i = 0 To .ViewList.ListCount - 1 'ViewList is your
> combobox
> .ViewList.Selected(i) = True
> If i > 0 Then .ViewList.Selected(i - 1) = False
> 'Call sub to produce your output to print
> Application.DisplayAlerts = False
> ActiveSheet.PrintOut
> Application.DisplayAlerts = True
> Next i
> End With
> Unload Userform1
> End Sub
>
> I don't know about the print buffer issue - depends on the printer
> memory whether it crashes or not?
> regards
> Paul
>
> rael_lucid wrote:
> > Paul,
> >
> > further to this, I have now completed the application. It is all controlled
> > from a single combobox and is working EXTREMELY well. The only problem I am
> > having is that the chart I have selected (3D pie) is sometimes overlapping
> > the data labels. Not a big deal, and the client is going to have to deal with
> > it.
> >
> > My real issue is (and I have created a separate post on this forum, but you
> > might be able to assist straight away) I now want to add a button so that the
> > user can "Print All". ie: I need the code to effectively select each value
> > from the combobox, one at a time, and print out the page. Is this possible?
> > Am I going to crash the computer/printer if the list is over 100 items long?
> > Is there a better way?
> >
> > Rael
> >
> > "rael_lucid" wrote:
> >
> > > Paul,
> > >
> > > You are so right. I realised this whilst developing the solution for my
> > > client. I was basically giving them exactly what they required, without
> > > suggesting improvements. They are a public company and were looking for a way
> > > of simplifying the printing of their financial statements on a monthly basis.
> > > It was taking them in excess of 2 weeks to prepare and print the reports. I
> > > have just returned from a meeting with them where I have offered (and they
> > > accepted) the following solution:
> > >
> > > Data pulled from existing databases into common MS Access DB.
> > > MS Access report to print profit and loss statements
> > > MS Excel Pivot Table to analyse reports.
> > >
> > > I think the initial mistake was trying to build a single solution that:
> > > 1) was easy to print all reports
> > > 2) was easy analyse and modify single reports
> > >
> > > Your advice only confirmed the decision.
> > >
> > > Thanks
> > >
> > > Rael
> > >
> > > "(E-Mail Removed)" wrote:
> > >
> > > > Hi
> > > > Would it make more sense to have your profit/loss data on one sheet and
> > > > bring that to your 3 charts? i.e. you would have ONE set of charts. Now
> > > > your problem is to efficiently bring the particular profit/loss info to
> > > > the data area for your charts so that you can see it. This might
> > > > involve creating a Userform that lets the user select the data they
> > > > need - once selected it might simply be copied to your data area for
> > > > the charts (so that the charts are updated). This should be easy to do,
> > > > so post back if you want some detail.
> > > > Doing things your way might well end up creating an enormous number of
> > > > graphic objects in your workbook, which will will slow it down horribly
> > > > as time goes on.
> > > > regards
> > > > Paul
> > > >
> > > > rael_lucid wrote:
> > > > > Hi,
> > > > >
> > > > > I have a worksheet which contains a number of Profit and Loss statements.
> > > > > Each one is of EXACTLY the same format (ie: same number of rows, account
> > > > > names, columns etc.
> > > > >
> > > > > Below the first profit and loss statement are 3 charts, each of which uses
> > > > > data from the statement above it to generate the lines etc.
> > > > >
> > > > > What I now want to do is programatically insert the same three charts below
> > > > > each of the subsequent profit and loss statements - the only change being the
> > > > > different data sources and perhaps the titles.
> > > > >
> > > > > I would do it manually however there are in excess of 75 profit and losses
> > > > > at the moment, and there is also the likelihood that the number will increase
> > > > > (dramatically). Given that each one is inserted using VBA, it seems logical
> > > > > to also insert the charts using VBA, using the original three as the starting
> > > > > points.
> > > > >
> > > > > I have no problem finding the insertion points, or even inserting a chart,
> > > > > but I am really struggling with:
> > > > > 1) copying and pasting the existing chart into a new location on the same
> > > > > sheet; and
> > > > > 2) changing the data source to a new range
> > > > >
> > > > > Can anyone help?
> > > >
> > > >

Paul,

Code is perfect for a ListBox, so, I have changed my control to a ListBox
and it is now working beautifully. On a bit of a tight timeline for my client
so will install with the listbox and do some more research on the relevant
code for a ComboBox ( .Selected is specifically for ListBoxes).

Have tried the process out with 10 choices and my $200 laser printer is
fine. Will see what happens tomorrow with 50 odd choices.

Thanks again for the help!

Rael

"(E-Mail Removed)" wrote:

> Hi
> Forgot to say that this sub would be called by the button on Userform1.
> regards
> Paul
>
> paul.robin...@it-tallaght.ie wrote:
> > Hi
> > Something like this code/pseudocode should do it (untested)
> >
> > Sub Print_All()
> > Dim i As Integer
> > Application.ScreenUpdating = False
> > 'Create a hidden version of the Form
> > Userform1.Hide
> > With Userform1
> > 'Select each list item in turn and make the output for that item
> > For i = 0 To .ViewList.ListCount - 1 'ViewList is your
> > combobox
> > .ViewList.Selected(i) = True
> > If i > 0 Then .ViewList.Selected(i - 1) = False
> > 'Call sub to produce your output to print
> > Application.DisplayAlerts = False
> > ActiveSheet.PrintOut
> > Application.DisplayAlerts = True
> > Next i
> > End With
> > Unload Userform1
> > End Sub
> >
> > I don't know about the print buffer issue - depends on the printer
> > memory whether it crashes or not?
> > regards
> > Paul
> >
> > rael_lucid wrote:
> > > Paul,
> > >
> > > further to this, I have now completed the application. It is all controlled
> > > from a single combobox and is working EXTREMELY well. The only problem I am
> > > having is that the chart I have selected (3D pie) is sometimes overlapping
> > > the data labels. Not a big deal, and the client is going to have to deal with
> > > it.
> > >
> > > My real issue is (and I have created a separate post on this forum, but you
> > > might be able to assist straight away) I now want to add a button so that the
> > > user can "Print All". ie: I need the code to effectively select each value
> > > from the combobox, one at a time, and print out the page. Is this possible?
> > > Am I going to crash the computer/printer if the list is over 100 items long?
> > > Is there a better way?
> > >
> > > Rael
> > >
> > > "rael_lucid" wrote:
> > >
> > > > Paul,
> > > >
> > > > You are so right. I realised this whilst developing the solution for my
> > > > client. I was basically giving them exactly what they required, without
> > > > suggesting improvements. They are a public company and were looking for a way
> > > > of simplifying the printing of their financial statements on a monthly basis.
> > > > It was taking them in excess of 2 weeks to prepare and print the reports. I
> > > > have just returned from a meeting with them where I have offered (and they
> > > > accepted) the following solution:
> > > >
> > > > Data pulled from existing databases into common MS Access DB.
> > > > MS Access report to print profit and loss statements
> > > > MS Excel Pivot Table to analyse reports.
> > > >
> > > > I think the initial mistake was trying to build a single solution that:
> > > > 1) was easy to print all reports
> > > > 2) was easy analyse and modify single reports
> > > >
> > > > Your advice only confirmed the decision.
> > > >
> > > > Thanks
> > > >
> > > > Rael
> > > >
> > > > "(E-Mail Removed)" wrote:
> > > >
> > > > > Hi
> > > > > Would it make more sense to have your profit/loss data on one sheet and
> > > > > bring that to your 3 charts? i.e. you would have ONE set of charts. Now
> > > > > your problem is to efficiently bring the particular profit/loss info to
> > > > > the data area for your charts so that you can see it. This might
> > > > > involve creating a Userform that lets the user select the data they
> > > > > need - once selected it might simply be copied to your data area for
> > > > > the charts (so that the charts are updated). This should be easy to do,
> > > > > so post back if you want some detail.
> > > > > Doing things your way might well end up creating an enormous number of
> > > > > graphic objects in your workbook, which will will slow it down horribly
> > > > > as time goes on.
> > > > > regards
> > > > > Paul
> > > > >
> > > > > rael_lucid wrote:
> > > > > > Hi,
> > > > > >
> > > > > > I have a worksheet which contains a number of Profit and Loss statements.
> > > > > > Each one is of EXACTLY the same format (ie: same number of rows, account
> > > > > > names, columns etc.
> > > > > >
> > > > > > Below the first profit and loss statement are 3 charts, each of which uses
> > > > > > data from the statement above it to generate the lines etc.
> > > > > >
> > > > > > What I now want to do is programatically insert the same three charts below
> > > > > > each of the subsequent profit and loss statements - the only change being the
> > > > > > different data sources and perhaps the titles.
> > > > > >
> > > > > > I would do it manually however there are in excess of 75 profit and losses
> > > > > > at the moment, and there is also the likelihood that the number will increase
> > > > > > (dramatically). Given that each one is inserted using VBA, it seems logical
> > > > > > to also insert the charts using VBA, using the original three as the starting
> > > > > > points.
> > > > > >
> > > > > > I have no problem finding the insertion points, or even inserting a chart,
> > > > > > but I am really struggling with:
> > > > > > 1) copying and pasting the existing chart into a new location on the same
> > > > > > sheet; and
> > > > > > 2) changing the data source to a new range
> > > > > >
> > > > > > Can anyone help?
> > > > >
> > > > >
>
>