Solved: VBA to create Charts for several sheets with different data lengths

[JimS]

I have a Workbook Template that is created by importing (copying other worksheets). The number of worksheets that get imported into Workbook Template can vary from 1 -32 sheets. Each sheet that gets imported comes in with the sheet name of Sheet1, Sheet1(2), Sheet1(3), Sheet1(4), Sheet1(5), etc. The data on each of these imported sheets can vary in size (vary in rows and columns).

I need to automatically create a Column Chart for each of the sheets that get imported.

Not sure if this is possible but knowing the talent and expertise of this forum I thought I would see if anyone has any ideas on how to accomplish this.

To make it harder (as if this isn’t hard enough), the number of Columns and Rows on any of the imported sheet can vary.

I do not want to chart all the columns, but only certain ones (and only if they exist on the individual sheet).

The data points will always start in Row 5. Using Columns A, C & D, I & J, O & P, U & V, etc, etc, etc (if these columns exist on the sheet).
There is always Column A and C & D, after that (if they exist) the next columns are 6 columns out, and then 6 more, and so on. Always 6 columns out from column C and 6 columns out from column D.

I manipulate the data on the imported sheets with various vba routines before the Column Charts can be created, but the last thing I do is rename each of the imported sheets (using a portion of what is in a cell on the sheet.
I write the new sheet names out to another sheet called Names – perhaps have a list of the sheets that need a Chart will help.

Does anyone have any ideas on how to go about this?

Thanks for any and all help…

I have attached a smaller example of how the imported sheets look.

Jim

[JimmyTheHand]

Hi

The code is below. But first, about the assumptions I made. I assumed that
data columns come in pairs, that is, if column C has data then column D also has data
data column headers (e.g. "\\ptvesx004.il.teva.corp\Physical Disk(vmhba1)\Writes/sec") are always of the same length
the datasheets will always be called like "ptvesx*" where * is anything. Only datasheets will be named along this convention. (Charts don't count as datasheets.)
all data below row 5 will be chartedThe resulting charts look like the example you posted, but there may be some setting you made but I didn't notice, so if you feel something is missing just tell me.

Sub PlotCharts()
Dim ChartRange As Range, BaseRange As Range, i As Long
Dim CH As Chart
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
If WS.Name Like "ptvesx*" Then
i = 1
Set BaseRange = WS.Range("A5", WS.Range("A" & WS.Rows.Count).End(xlUp))
Set ChartRange = BaseRange
Do
If WS.Range("A5").Offset(, i * 6 - 4).Value = "" Then Exit Do
Set ChartRange = Union(ChartRange, BaseRange.Offset(, i * 6 - 4).Resize(, 2))
i = i + 1
Loop While i < 43
Set CH = ThisWorkbook.Charts.Add
With CH
.ChartType = xlColumnClustered
.SetSourceData Source:=ChartRange, PlotBy:=xlColumns
.Location Where:=xlLocationAsNewSheet, Name:=WS.Name & " Chart"
.PlotArea.Interior.ColorIndex = 2
.PlotArea.Width = CH.ChartArea.Width - 15
With .Legend
.Top = 10
.Left = CH.Axes(xlValue).Left + 10
.Height = (i - 1) * 24
.Width = 300
.Shadow = False
.Interior.ColorIndex = xlNone
End With
End With
End If
Next
End Sub

Jimmy

[JimS]

Jimmy,

Thanks for the help...

In response to your assumptions:

1) If there is a column C there will be a Column D - yes they are in pairs.
2) The Column Headers will not always be the same length - but the columns that need to be charted will always end with "Reads/sec" & "Writes/sec".
3) Not sure what you mean by "like". They will be similar named but will not be "ptvesx*" every time. Perhaps using the sheet names on the "Names" sheet (starting at cell A4 as a reference would be easier?
4) Correct the data to be charted will always be below row 5.

Thanks again for your help.

Jim

[JimmyTheHand]

Jim

the code has been altered a little.

Sub PlotCharts()
Dim NameRange As Range, NameCell As Range
Dim ChartRange As Range, BaseRange As Range, i As Long
Dim CH As Chart
Dim WS As Worksheet

Set NameRange = ThisWorkbook.Worksheets("Names").Range("A4")
Set NameRange = Range(NameRange, NameRange.End(xlDown))
For Each NameCell In NameRange.Cells
Set WS = ThisWorkbook.Worksheets(NameCell.Value)
i = 1
Set BaseRange = WS.Range("A5", WS.Range("A" & WS.Rows.Count).End(xlUp))
Set ChartRange = BaseRange
Do
If WS.Range("A5").Offset(, i * 6 - 4).Value = "" Then Exit Do
Set ChartRange = Union(ChartRange, BaseRange.Offset(, i * 6 - 4).Resize(, 2))
i = i + 1
Loop While i < 43
Set CH = ThisWorkbook.Charts.Add
With CH
.ChartType = xlColumnClustered
.SetSourceData Source:=ChartRange, PlotBy:=xlColumns
.Location Where:=xlLocationAsNewSheet, Name:=WS.Name & " Chart"
.PlotArea.Interior.ColorIndex = 2
.PlotArea.Width = CH.ChartArea.Width - 15
With .Legend
.Top = 10
.Left = CH.Axes(xlValue).Left + 10
.Height = (i - 1) * 24
.Width = 300
.Shadow = False
.Interior.ColorIndex = xlNone
End With
End With
Next
End Sub

Now it takes worksheet names from Column A in Sheet("Names"). About my assumptions:
1) It's fine.
2) Column header length is important only because the width of the chart legend. The code sets it to a fixed 300. It may not be enough if header strings are longer than in the example. I don't know of a reliable way to determine a string's length in pixels.
3) It's irrelevant now.
4) What I really meant was that, by the code, all data below, and including, row 5 WILL be charted. It may lead to false charts if, for example, there's a summary row at the bottom.

Jimmy

[JimS]

Jimmy,

Thank you very much - this will work perfectly...

Item 2) Column Header Length should not be much longer so that should not be an issue.

Item 4) There will be no total rows at the bottom - only actual data points that should be included in the charts

Thanks again...

Jim