Charts! AHHH1

[jstevens]

Alright, so here is my delimma, I have 100 worksheets in a workbook. Everyother sheet contains data, each of these data sheets contains exactly the same columns rows,, headings, etc, formatted the same and all that. In the empy sheets next to these data sheets (once again, every other one) I need to chart said data. The range for the charts will be the same on each data sheet, the charts will represent the same type of data, with the same lables, same colors, everything. the only thing that changes from data sheet to data sheet and from chart to chart is the heading (the well name) and the actual data. So, instead of using the chart wizard, 50 some odd times, I wanted to make a macro that would make my chart. So what I did was to record the above macro. I recorded it making the first chart exactly how I needed it to appear on all of the sheets, using the first data set as an example. But...then when I run the macro for the other sheets, it of course recorded my "exact" key strokes, so it just remakes the exact same chart in the same worksheet with the same data as before, (obviously because this is what a macro does, but...). So what I am looking for is a way to modify this macro above to say for example, in lay terms...."put the chart in the active worksheet i.e. the one with my cursor in it...not "Sheet3" as above, and pull the data from the range mentioned above, but from the "active worksheet + 1" or the worksheet directly after the one with my cursor in it....this way, I can run the macro on each of my "blank pages" and it will make my chart, pulling data from the page to the right...basically, the above macro, but with, variable references for the data instead of a fixed one.

I hope that makes sense...i am sure that anyone who knows a little about VB could come in and just type some code in there and make it work perfect, but I have been all over the internet reading and asking everyone in the office, and no one knows anything. So here's my last chance before i just bite the bullet and pull up my chart wizard and a pillow!!!

here's the code......


Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/10/2006 by JStevens
'
' Keyboard Shortcut: Ctrl+a
'
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1") (need this one variable as "active sheet")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "='Bishop 1-17'!R2C2:R32C2" (need this one variable as well "active sheet+1")
ActiveChart.SeriesCollection(1).Values = "='Bishop 1-17'!R2C7:R32C7"
ActiveChart.SeriesCollection(1).Name = "=""MCF/D"""
ActiveChart.SeriesCollection(2).XValues = "='Bishop 1-17'!R2C2:R32C2"
ActiveChart.SeriesCollection(2).Values = "='Bishop 1-17'!R2C4:R32C4"
ActiveChart.SeriesCollection(2).Name = "=""Static"""
ActiveChart.SeriesCollection(3).XValues = "='Bishop 1-17'!R2C2:R32C2"
ActiveChart.SeriesCollection(3).Values = "='Bishop 1-17'!R2C5:R32C5"
ActiveChart.SeriesCollection(3).Name = "=""Diff."""
ActiveChart.SeriesCollection(4).XValues = "='Bishop 1-17'!R2C2:R32C2"
ActiveChart.SeriesCollection(4).Values = "='Bishop 1-17'!R2C8:R32C8"
ActiveChart.SeriesCollection(4).Name = "=""Csng PSI"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1" (this is probably some key problem)
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Bishop 1-17 Monthly Production Data"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveWindow.Visible = False
Windows("Production Charts.wells.baca.xls").Activate
Range("D14").Select
End Sub

[Webtest]

jstevens:

I'm no pro on charts, but here are a few hints that may help you. You may or may not want to 'Activate' sheets as the macro progresses ... it takes longer to set up the display for every sheet ... but at least you can see that it is progressing. You will at least want to turn off the display update while it is creating each page: Application.ScreenUpdating = [True | False]

You can also access sheets by name or by index, which is a handy feature, except that you seem to be creating the Chart sheets on the fly, which means (I think) that the sheet indices are constanly changing(?). I just checked and Sheets and Charts are handled separately ... oops ... not exactly! Adding Charts still fouls up the indexing for sheets(?). Also, a loop to iterate through "Sheets" still sees a "Chart" as a "Sheet" that isn't in the "Sheets" collection so it gets very confused.

If you are not confused yet, you should be! Since the "Sheets" collection includes all "Sheet" objects AND all "Chart" objects, you are going to have to differentiate between a Sheet and a Chart with a test on each object in the "Sheets" collection.

What is the naming convention for your 100 source data sheets? That could be very important! You may be able to differentiate a Sheet from a Chart if you know what to expect as a Sheet.Name.

Let me post this now and get back shortly with coded loop ... I'm having trouble with the code, but I'm close.

[jstevens]

Thanks so much for getting back to me!!
i don't know if this changes things at all or not, but in reality, the charts will be inserted as objects in a blank sheet, so the loop should be able to just encompass "sheets" and not worry about what is in the sheet? Yeah, i'm pretty confused...
As for the naming, each data sheet (which is ever other sheet, as the charts will be inserted in the other sheet next to the one containing data, so it will be chart, data, chart, data....etc.) will be named after a well...I work for an oil and gas company...so there is no "series" or attributal pattern to the naming if that was what you were perhaps hoping for. That's why I need the macro to be variable to the "active sheet" which ever sheet i am on when i run the macro is the sheet that i need the chart to be created in, and i need it to pull the data from the sheet right next to it (to the right...)

i hope this helps explain a little my dilemma! Thanks for your time, really!

[jstevens]

one more thing....
i already have the workbook set up exactly as I need it, I have all the data sheets already entered, and I have left a blank sheets in between for the charts. So there will be no altering of the workbook...I don't even know if it is possible, but what i would like to do is be able to update the data sheets (adding new data to the old data going down the column, thus extending my chart's data range) and have the chart graph the new data with the old data "automatically"

[Webtest]

OK ... some test code first, and then notes ...
View > Immediate Window to see Debug output

Option Explicit
'With this option, you must provide a "Dim" statement for every variable used!
'
Sub test()
    Dim aShtObj As Object
    Dim aSheet As Worksheet
    Dim aChart As Chart
    Dim i_Sheets As Integer
    Dim i As Integer
    
    For Each aShtObj In ActiveWorkbook.Sheets
        
        Debug.Print aShtObj.Type
        
        If aShtObj.Type = 3 Then  'xlChart does NOT work! See the Debug line above
            Set aChart = aShtObj
            Debug.Print "Type CHART: ", aChart.Name
        End If
        If aShtObj.Type = xlWorksheet Then
            Set aSheet = aShtObj
            Debug.Print "Type SHEET: ", aSheet.Name
        End If
        
    Next aShtObj

    '   ----  OR  ----  another way

    i_Sheets = ActiveWorkbook.Sheets.Count
    Debug.Print "Number of SHEETS", i_Sheets
    
    For i = 1 To i_Sheets
        Debug.Print i, Sheets(i).Name
        'Here you can differentiate by Type (as above) or by Name or whatever
        'Do your thing with the sheet
    Next i
    
End Sub

I think it is a good thing that all of the sheets are set up before you start writing charts ... that means that the indexing of the Sheets collection doesn't change while you are working on it. However, rather than have all Worksheets (with charts on some), you may want alternating Sheet objects and Chart objects. I don't really know. Anyway, in the code above, if there are Worksheets and Chart(sheets) you can iterate through all Sheets and only work on the Worksheets (Data sheets). The variable "aSheet" is a handle for each sheet ... aSheet.Name is the tab name for the sheet. If you can determine the Chart sheet name from the Data sheet name, then you are in good shape because you can do something like the following:

Dim tStr As String 'Temp String
'  ...
tStr = aSheet.Name
tStr = Replace(tStr, "Data", "Chart")
Set aChart = charts(tStr)

Now you can refer to the Data sheet generically as "aSheet", and the equivalent Chart sheet as "aChart". Does this get you any farther along?

[Webtest]

This is a possible basic framework if you have a workbook of DATA sheets ONLY ... no Chart sheets. It will add a new Chart sheet after each data sheet. It quits if it finds any sheets that are not type Worksheet but if you want, you could just delete the old sheets and create new ones. Or, you could write a prefix loop to dump all old chart sheets so that this loop starts off fresh with only Data sheets. It will fail if you try to create a chart sheet with the same name as an existing sheet ...

Option Explicit
Sub Macro1()

Dim aShtObj As Object
Dim aChart As Chart
Dim aSheet As Worksheet
Dim tStr As String  'Temp String

    For Each aShtObj In ActiveWorkbook.Sheets
        If aShtObj.Type = xlWorksheet Then
            'This sets generic handles for the Data sheet and the new Chart sheet
            Set aSheet = aShtObj
            Set aChart = Charts.Add
            'Rename the Chart sheet to match the Data sheet
            tStr = aSheet.Name
            tStr = Replace(tStr, "Sheet", "Chart")  '< I used default sheet names
            aChart.Name = tStr
            'Here is where you build the chart from the data sheet
            'Put your own stuff here ... this is just test junk
            ActiveChart.ChartType = xlColumnClustered
            ActiveChart.SetSourceData Source:=Sheets(aSheet.Name).Range("A1")
            ActiveChart.Location Where:=xlLocationAsNewSheet
            ActiveChart.HasLegend = True
            'ActiveChart.Legend.Select
            'Selection.Position = xlRight
           Sheets(aChart.Name).Move After:=Sheets(aSheet.Name)
        Else
            aShtObj.Select
            MsgBox "This Sheet is not a WORKsheet ... maybe it is a CHART sheet?" & Chr(10) & Chr(10) _
            & aShtObj.Name
            End
        End If
    
    Next aShtObj
End Sub

[zaza]

Hi,

You may be also interested in this.


zaza