Chart Tab Name & Worksheet Reference

[bob]

This is part of a macro that draws a chart. I need to fix this code so that
it will reference the named worksheet, clear the chart worksheet, name the
chart tab with: Chart(samenameasworksheet). I also need it to clear the
chart worksheet and tab name, so if the macro is run a second time, it will
use the same chart worksheet.
If you look at line 4 of the chart code, =Sheets refers to "AAA" which is
one filename out of a possible 5,000 or more files. I tried referring to
mydatafile, which is what is used in the macro part that gets the data and
filename in the first place, but this did not work. I'm only a beginner
with VBA and put most of this together using snippets of code found on this
news groups and by using the macro-recorder. Where I ALWAYS seem to get
stuck is modifying the path that is referenced, so if some kind soul out
there would like to give me or point to a tutorial that could explain this,
I'd be forever grateful. Thanks.

I also included part of the first portion of the macro that opens the
worksheet and names the tab with the .txt filename less the .txt extension;
but first here is the code that draws the chart below:
Range("A1:A50,E1:E50,H1:H50").Select
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData
Source:=Sheets("AAA").Range("A1:A50,E1:E50,H1:H50") _
, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).AxisGroup = 2
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.Axes(xlCategory).Select
With Selection.TickLabels
.Alignment = xlCenter
.Offset = 100
.ReadingOrder = xlContext
.Orientation = xlUpward
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.DisplayUnit = xlNone
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
ActiveChart.PlotArea.Select
With Selection.Border
.ColorIndex = 16
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection.Interior
.ColorIndex = 2
.PatternColorIndex = 1
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.Axes(xlValue).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlNone
.MinorTickMark = xlNone
.TickLabelPosition = xlNone
End With
ActiveChart.Axes(xlValue).MajorGridlines.Select
With Selection.Border
.ColorIndex = 57
.Weight = xlHairline
.LineStyle = xlDot
End With
ActiveChart.Axes(xlValue, xlSecondary).Select
ActiveChart.ChartArea.Select
ActiveChart.Deselect
End Sub

Here is the worksheet part of the macro below:
Dim shArr As Variant
Dim shName As String

'This part clears the data worksheet
Cells.Select
Selection.ClearContents
Range("A1").Select

'This part gets file from data directory

MyDatafile = Application.GetOpenFilename("Text Files,*.Txt")

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & MyDatafile, _
Destination:=Range("A1"))
.Name = "MyDataFile & Activesheet.QueryTable.counts +1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

End With
shArr = Split(MyDatafile, "\")
shName = shArr(UBound(shArr))
If InStr(shName, ".") Then _
shName = Left(shName, InStr(shName, ".") - 1)
ActiveSheet.Name = shName

 

[Mike Fogleman]

Dim shName at the top of the code module, before any macros. This will
retain the variable for use in any macro within this code module. Now you
can substitute that variable into line 4 of the chart code as long as shName
obtained a value before the chart code is run. if you Dim a variable within
the macro, it will lose it's value when the macro ends. Read VB Help on the
'life of a variable' for more tips.
Mike F

 

[bob]

So after I declare shName, I should replace "AAA" with shName? Is that all
that there is to it?

Thanks

 

[bob]

Mike: I just checked, and I already have Dim shName at the beginning of the
macro. If you scroll down through the oriignal message a bit you'll see I'm
using shName for
getting the data intot the worksheet.
Thanks