如何動辨別有多少sheet畫幾次圖

iverson105

各位大大好

因工作需求需要建立一個自動繪圖的VBA程式
已在此站上尋獲可自動輸入資料的方式(感謝大大)
但問題是每次輸日的資料多少不一定(ps:一筆資料,一個sheet;有時3個sheets 有時5個sheet,最多不超過10組)
每個sheet要畫10張圖
我已錄製繪圖的巨集,但要只會重複固定的sheet(ex: sheet1......plot;  sheet2......plot, sheet3...plot''''''停止)
但會因為每次資料sheet多少不一定;而無法寫死plot在一定的sheets數量(有時3sheets, 有時4sheets, 有時6sheets,  最多10sheets)
請問該如何解阿
以下附上程式
希望格位大大幫忙

                                    5.畫圖.....................................................主要是這一塊(都劃在一張sheet上),想自動辦別有多少sheet而畫幾次
     Sub Plot()
'''''''''''''''''''''''''''''''''''''''''Chart(1) Ave. G.R. / HSP ADD'''''''''''''''''''''''''
    Sheets("工作表1 (2)").Activate
    Range("AA20").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
   
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "=""GSP"""
    ActiveChart.SeriesCollection(1).XValues = "='工作表1 (2)'!$AA$3:$AA$17"
    ActiveChart.SeriesCollection(1).Values = "='工作表1 (2)'!$AB$3:$AB$17"
      
    ActiveChart.SeriesCollection(1).Select
    With Selection.Format.Line
    .Visible = msoTrue
    .ForeColor.ObjectThemeColor = msoThemeColorText1
    End With
   
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "=""HGSP"""
    ActiveChart.SeriesCollection(2).XValues = "='工作表1 (2)'!$AA$3:$AA$17"
    ActiveChart.SeriesCollection(2).Values = "='工作表1 (2)'!$AC$3:$AC$17"
    ActiveChart.SeriesCollection(2).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
    End With
   
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).Name = "=""LGSP"""
    ActiveChart.SeriesCollection(3).XValues = "='工作表1 (2)'!$AA$3:$AA$17"
    ActiveChart.SeriesCollection(3).Values = "='工作表1 (2)'!$AD$3:$AD$17"
    ActiveChart.SeriesCollection(3).Select
    With Selection.Format.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 0, 0)
    End With
   
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(4).Name = "=""Ave.G.R."""
    ActiveChart.SeriesCollection(4).XValues = "='工作表1 (2)'!$A$2:$A$20000"
    ActiveChart.SeriesCollection(4).Values = "='工作表1 (2)'!$D$2:$D$20000"
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(5).Name = "=""HSP"""
    ActiveChart.SeriesCollection(5).XValues = "='工作表1 (2)'!$A$2:$A$20000"
    ActiveChart.SeriesCollection(5).Values = "='工作表1 (2)'!$O$2:$O$20000"
    ActiveChart.SeriesCollection(5).AxisGroup = 2
  ActiveChart.ApplyLayout (4)
   Dim xRg As Range
     Dim xChart As ChartObject
    Set xRg = Range("AA20:AG40")
  Set xChart = ActiveSheet.ChartObjects(1)
   With xChart
  .Top = xRg(1).Top
.Left = xRg(1).Left
.Width = xRg.Width
.Height = xRg.Height
   End With


ActiveChart.SetElement (msoElementChartTitleAboveChart)
    Selection.Caption = "Ave.G.R. vs HSP"

        
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    Selection.Caption = "G.R.(mm/hr)"
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    Selection.Caption = "Length(mm)"
        
   
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 0
    ActiveChart.Axes(xlCategory).MaximumScale = 1100
    ActiveChart.Axes(xlCategory).MajorUnit = 100
    ActiveChart.Axes(xlCategory).MinorUnit = 50
    ActiveChart.Axes(xlCategory).CrossesAt = 0

    ActiveChart.SetElement (msoElementPrimaryValueGridLinesMajor)
    ActiveChart.SetElement (msoElementPrimaryCategoryGridLinesMajor)

Sheets("工作表1").Activate


'''''''''''''''''''''''''''''''''''''''''Chart(2) HSP'''''''''''''''''''''''''

   Sheets("工作表1 (2)").Activate
   Range("AI20").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers

    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "=""Delta_HSP"""
    ActiveChart.SeriesCollection(1).XValues = "='工作表1 (2)'!$AK$3:$AK$17"
    ActiveChart.SeriesCollection(1).Values = "='工作表1 (2)'!$AM$3:$AM$17"
    ActiveChart.SeriesCollection.NewSeries

    ActiveChart.SeriesCollection(2).Name = "=""Delta_HSP(SOP)"""
     ActiveChart.SeriesCollection(2).XValues = "='工作表1 (2)'!$AK$3:$AK$17"
    ActiveChart.SeriesCollection(2).Values = "='工作表1 (2)'!$AN$3:$AN$17"

    ActiveChart.ApplyLayout (4)
   Dim yRg As Range
     Dim yChart As ChartObject
    Set yRg = Range("AI20:AN40")
  Set yChart = ActiveSheet.ChartObjects(2)
   With yChart
  .Top = yRg(1).Top
.Left = yRg(1).Left
.Width = yRg.Width
.Height = yRg.Height
   End With
   
    ActiveChart.SetElement (msoElementChartTitleAboveChart)
    Selection.Caption = "Delat_HSP"
   
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    Selection.Caption = "Delta_HSP(sp)"
   
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    Selection.Caption = "Length(mm)"
   
   
    ActiveSheet.ChartObjects(2).Activate
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 0
    ActiveChart.Axes(xlCategory).MaximumScale = 1100
    ActiveChart.Axes(xlCategory).MajorUnit = 100
    ActiveChart.Axes(xlCategory).MinorUnit = 50

   
    ActiveChart.SetElement (msoElementPrimaryValueGridLinesMajor)
    ActiveChart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
   

'''''''''''''''''''''''''''''''''''''''''''''''''''Chart(3)Pressure torr ADD'''''''''''''''''''''''''

Sheets("工作表1 (2)").Activate

   Range("AP20").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Name = "=""F/T Press."""
    ActiveChart.SeriesCollection(1).XValues = "='工作表1 (2)'!$A$2:$A$20000"
    ActiveChart.SeriesCollection(1).Values = "='工作表1 (2)'!$I$2:$I$20000"

    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(2).Name = "=""Press. SP"""
    ActiveChart.SeriesCollection(2).XValues = "='工作表1 (2)'!$A$2:$A$20000"
    ActiveChart.SeriesCollection(2).Values = "='工作表1 (2)'!$P$2:$P$20000"
   
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).Name = "=""Press._SOP"""
    ActiveChart.SeriesCollection(3).XValues = "='工作表1 (2)'!$AP$3:$AP$17"
    ActiveChart.SeriesCollection(3).Values = "='工作表1 (2)'!$AT$3:$AT$17"

    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(4).Name = "=""Throttle Pos.(%)"""
    ActiveChart.SeriesCollection(4).XValues = "='工作表1 (2)'!$A$2:$A$20000"
    ActiveChart.SeriesCollection(4).Values = "='工作表1 (2)'!$T$2:$T$20000"
    ActiveChart.SeriesCollection(4).AxisGroup = 2

   ActiveChart.ApplyLayout (4)
   Dim zRg As Range
     Dim zChart As ChartObject
    Set zRg = Range("AP20:AU40")
   Set zChart = ActiveSheet.ChartObjects(3)
   With zChart
  .Top = zRg(1).Top
  .Left = zRg(1).Left
  .Width = zRg.Width
  .Height = zRg.Height
   End With



    ActiveChart.SetElement (msoElementChartTitleAboveChart)
    Selection.Caption = "Press. vs Throttle Position Variation"
   
    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
    Selection.Caption = "Press.(torr)"
   
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    Selection.Caption = "Length(mm)"
   

    ActiveSheet.ChartObjects(3).Activate
    ActiveChart.Axes(xlCategory).Select
    ActiveChart.Axes(xlCategory).MinimumScale = 0
    ActiveChart.Axes(xlCategory).MaximumScale = 1100
    ActiveChart.Axes(xlCategory).MajorUnit = 100
    ActiveChart.Axes(xlCategory).MinorUnit = 50
    ActiveChart.SetElement (msoElementPrimaryValueGridLinesMajor)
    ActiveChart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
   
   
    End Sub