Automate displaying of filtered graphs

[gautammalkani]

Hi

Currently, when I run this code, I cant seem to paste special my filtered graph. I am trying to automate a process where all the filtered graphs are placed on another worksheet in the same file. Currently, I am looping for 5 different filtered locations as a test to see if my code works. I have attached my code. Please let me know if you can help me in any way possible. Thanks

Gautam

Here is my code and I get an error on the "activesheet. paste special" THe error states that it is unable to get the chartsobject property of the Worksheet class


Sub graph()

' graph Macro
' Macro recorded 9/22/03 by MALKAG
' This macro changes the name of the chart to
' what is selected using the drop down menu and sends the charts
' to another work sheet
' Press the customized macro button (smiley face)
' to apply the macro

Dim word As String
Dim heading As String
Dim i, j, k As Integer
i = 2
Dim Clli(200) As String

Sheets("0603_WcUtil&quot.Select
Range("C1&quot.Select
For j = 1 To 5
k = 2
Clli(1) = Range("C2&quot.Value
Do While Not IsEmpty(Range("C" & k))
If Range(&quot;C&quot; & k).Value <> Clli(k - 1) Then
Clli(k) = Range(&quot;C&quot; & k).Value
End If
Selection.AutoFilter Field:=3, Criteria1:=Clli(j)
If Rows(2).Hidden = False Then
word = Range(&quot;C&quot; & i).Value
heading = &quot;T3 UTILIZATION &quot; & word
Else
Do Until Rows(i).Hidden = False
i = i + 1
word = Range(&quot;C&quot; & i).Value
heading = &quot;T3 UTILIZATION &quot; & word
Loop
End If
ActiveSheet.ChartObjects(&quot;Chart 45&quot.Activate
ActiveChart.ChartTitle.Select
ActiveChart.ChartTitle.Caption = heading
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Copy
ActiveWindow.Visible = False
Windows(&quot;final data15.xls&quot.Activate
Sheets(&quot;Sheet1&quot.Select
Range(&quot;A&quot; & (((i - 2) * 17)) + 1).Select
ActiveSheet.Paste
ActiveSheet.ChartObjects(&quot;Chart &quot; & (174 + j)).Activate
ActiveWindow.Visible = False
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
ActiveSheet.PasteSpecial Format:=&quot;Picture (Enhanced Metafile)&quot;, Link:=False _
, DisplayAsIcon:=False
Windows(&quot;final data15.xls&quot.Activate
Sheets(&quot;0603_WcUtil&quot.Select
Range(&quot;C1&quot.Select
k = k + 1
Loop
Next j
End Sub

 

[SkipVought]

gautammalkani,
Please clarify. What is the final objective?

Do you want all your charts on a single sheet? Or copies of your 5 charts on a single sheet? I think that your objective may be...

1. Choose a filter set
2. Copy the 5 charts to a new sheet
3. Do the above n times

You may want to do something like this...

Sub test()
    Set wsCharts = Workbooks("final data15.xls").Worksheets("FinalData")
    For Each ws In Worksheets
      With ws
        Select Case .Name
          Case "Sheet1", "Sheet2" ',... for your 5 chart sheet names
            For Each co In .ChartObjects
              fname = "C:\Temp\" & ActiveSheet.Name & co.Name & ".gif"
              co.Chart.Export fname, "GIF"
              With wsCharts
                ' calculate i for proper row
                .Cells((((i - 2) * 17)) + 1, "A").Select
                .Pictures.Insert(fname).Select
            Next
        End Select
      End With
    Next
End Sub

Of course, you'd have to add your filtering that would correspond to each ws.

BTW, you can and should do most of your coding WITHOUT activating and selecting unless necessary (faq707-4105 How Can I Make My Code Run Faster?)

Hope this helps



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[gautammalkani]

Hi

Basically, i have a hundred or so records that are filtered by location (clli). Once you apply the filter, a corresponding chart is generated on the same worksheet. I would like to generate all the filtered charts on a second worksheet with corresponding chart titles (ie cllis). I have the name working, but I couldnt paste a copy of the chart to the next page since the chart would be modified based on the filter being reapplied. Thus, I would get duplicates of the latest filtered chart. Thats why I used paste special. I would copy the chart to the next sheet and paste it and then cut it and paste special it so that it couldnt be modified. Hope that clarifies what I mean.

 

[SkipVought]

Then just use the Export/Insert method in lieu of copy/paste

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com