i wonder if anyone can help.
i have sheet1 called Data. and on here i have ever growing tables of data for our clients.
i would like the following sheets to contain graphs for each client.
so is there a vba which i could use that picks out a clients data range and creates a graph and drops it into their own worksheet?
thanks for your help
More than likely but we would need more information before being able to provide any more.
Have you tried using the macro recorder?
Do you need multiple charts? Perhaps 1 chart with the data filtered by client would suffice.
ok. so i could maybe have 1 chart and somehow filter the data for the client i wish to see?
you'll have a better idea of my request if i upload the spreadsheet....
you'll notice that i have a (just about working) code for printing all the charts to PDFs... if i was to use 1 graph and autofilter, is there still a way to print to pdf for all my clients individually?
See attached
This code produces a chart for each client.
The sheet name is based on Policy number for avoid duplicates in names.
If the sheet for a policy exists it will update the chart. If not a new sheet is created based on the ChtTemplate sheet.
that is fantastic... exactly what i needed.
there are 2 other things i'd like aswell on these generated sheets, i wonder if it can be incorporated into the same action.
1. a table of the same data in the chart
2. our company logo in the top left of the sheet.
is this possible at a click of a button too?
EDIT: can you confirm that when i update charts (with the code you provided to me above) it will resize the chart if necessary?
Last edited by mania112; 05-31-2009 at 10:58 AM.
Data is now copied to local sheet and chart uses local data.
You can stick you logo on the chart template sheet.
I have already placed a button on the data sheet.
What do you mean by resize?
by resize i mean 2 things.
the data is our clients funds, so if they're going down, we'd like to make sure the line isnt too steep... so my 'trick' usually is to increase the data range, so the line is never looks too dramatic.
the second question re: resizing is too automatically increasing the possibility of future data... so in this case can we say data in 2010 will be shown on the graph?
The macro updates the sheet, or creates if not already existing, with complete data from Data worksheet.
Yeah that first point is a data distortion so I let you workout how you do that.
If you really don't what you clients to see the trend displayed by the chart why give them a chart just use a table of numbers?
it's a question i ask myself! I just do what i'm told
thanks for all your help, you've given me a great foundation to build on there and saved me a week a month preparing all these (there are many other spreadsheets like this one).
I do have 1 very minor question:- how can i set every single sheet as landscape?
If you format the ChtTemplate sheet then run the macro the newly created sheets should retain the formatting, including page layout.
2 more things before i leave you alone!
1. I see how to move the table to a different cell (and how to find data again to make the graph) but is there a way to make the table vertical down a column rather than along a row. I would like the end result to be a graph and table side by side in landscape view.
2. Can you show me how to put the 'original transfer value' in a cell on the clients' sheet too - for arguements sake D32 in each sheet.
Thanks again for this
Please Login or Register to view this content.
no worries
Last edited by mania112; 06-01-2009 at 08:59 AM. Reason: found the problem!
The function accepts as it's argument the Policy Number. Previously that was located in column E of the row being processed. Is that still the case?
Also the value in column E is used as the sheet name so should be a unique value and one that is valid as a tab name. No special characters and less the 31 characters.
yeah it's ok thanks, i just missed copying across part of the code.
when i put across another formula is there a way to hide the #DIV/0! results (which will become populated in the future?
you would need to wrap your formula in IF() formula, which handles any errors.
thanks i'll look into that.
1 final point that i've just realised i've forgotten about
on the graph we have a second, static line identifying the 'original transfer value' - is there a simple way to maybe link to that value which lies in D32 at the moment?
You would need to add another series to the charttemplate.
The code places the OTV in column C
Please Login or Register to view this content.
I've updated my template, and attempted to move the table to the desired location within the sheet.
A problem in the code has popped up, i wonder if you could show me the error of my ways, with the attached file
EDIT: I wanted to move the 'original transfer value' into column L which is intentionally hidden in chttemplate
thanks
This code change will but the values for OTV in column L rather than C.
Please Login or Register to view this content.
You will also need to make a change to the chart on the template sheet.
Currently it has Plot visible cells only option set, which means even with the values in the right place the line will not be displayed.
Select the chart, Tools > options > Chart > Plot visible cells only.
there seems to be a problem here:
With shtPolicy.ChartObjects(1).Chart
.ChartTitle.Text = Left(rngRow.Cells(1, 2).Value, 1) & " " & rngRow.Cells(1, 1).Value & " " & _
rngRow.Cells(1, 4) & " " & rngRow.Cells(1, 5).Value
it doesnt complete the code, and the word LEFT here is highlighted
please use code tags.
Your file included a reference to pdfcreator, which for pc's without will create a Missing reference and cause error reporting of normally working functions.
In vbe use Tools > References to remove link.
ok thanks, everything looks like it's complete now.
I've found from Ron De Bruins site how to send each sheet by email, but is there a way to send it as a pdf rather than xls?
I'd rather not allow the clients to edit their data, and it's better presented in pdf format (or something similar)
this is the code for emailing:
Please Login or Register to view this content.
Ron has an example of sending PDF, for xl2007.
Otherwise you will first need to generate the pdf
Then email with attachment, Ron has code for this too.
Ron's the man!
however, if i want to create a pdf for multiple sheets then email them to different clients it would somehow be a combination of the code above and the 'mail PDF' section in http://www.rondebruin.nl/pdf.htm
I think Ron's code there only includes 1 default receipient?
You would need to loop through the same way the charts are looped.
Either create pdf and email within the loop or does it separately.
So 3 distinct loops,
make charts
make pdfs
email pdfs
i dont think i could code that without some help!?
It would be something very close to this.
Please Login or Register to view this content.
thanks that looks great, there is an error here tho:
thanks that looks great, there is an error here tho:Please Login or Register to view this content.
one of them 'sub or function not defined' errors, i think that's something to do with references? i have pdfcreator added as reference, but is there also a reference there for the emailing porion maybe?
check the list of references for a Missing item
VBE > Tools > References.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks