Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Unanswered: macros: adding a chart to sheets with predefined names?

    Im pretty new to using macros but have some VB experience, but it was quite a while ago and it was just high school CS. Basically I recorded a macro, and want to apply it to other files as well. Its lab data, there is 1 sheet per file and the sheet is named with the file name, in the below case 'axc160'. How can I make this macro run on a sheet with whatever name? Thanks.
    ==============================

    Range("B2:B601").Select

    Range("B2:B601,D2:D601").Select
    Range("D2").Activate

    Charts.Add
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Sheets("axc160").Range("B2:B601,D2:D601"), _
    PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="axc160"
    With ActiveChart
    .HasTitle = False
    .Axes(xlCategory, xlPrimary).HasTitle = False
    .Axes(xlValue, xlPrimary).HasTitle = False
    End With

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Re: macros: adding a chart to sheets with predefined names?

    Hi

    Had a quick look and put this bit of code together.
    This presumes you want the Chart on the same sheet as you run the Macro on and that that sheet is active.

    Code:
        Dim MySheet As String
        
        MySheet = ActiveSheet.Name
        Range("B2:B601,D2:D601").Select
        Range("D2").Activate
    
        Charts.Add
        ActiveChart.ChartType = xlLine
        ActiveChart.SetSourceData Source:=Sheets("axc160").Range("B2:B601,D2:D601"), _
        PlotBy:=xlColumns
        ActiveChart.Location Where:=xlLocationAsObject, Name:=MySheet
        With ActiveChart
            .HasTitle = False
            .Axes(xlCategory, xlPrimary).HasTitle = False
            .Axes(xlValue, xlPrimary).HasTitle = False
        End With
    Hope this Helps
    David

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    The other way is to put the code into the Personal.xls file,a nd it will be available whenever Excel is open.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •