Macro problems with range and naming issues

[Vapanda]

Is it possible where i can create a macro that automates graphs. Th
problems that i am having is that:

1) The workbook name ( i don't want it to work just for that workbook
but the macro sets the work book name)

2) The range of the data that I can use (again the macro sets th
data... I tried to use another code to modify this but i am having n
success).

Option Explicit

Sub SelectingRange()
Dim lngRow As Long
Dim myTotalRow As Long
Dim lngCol As Long
Dim myTotalCol As Long

' counting rows
lngRow = 2
Do While Cells(lngRow, 1) <> ""
lngRow = lngRow + 1
Loop
myTotalRow = lngRow - 1

' counting columns
lngCol = 2
Do While Cells(1, lngCol) <> ""
lngCol = lngCol + 1
Loop
myTotalCol = lngCol - 1
ActiveSheet.Range(Cells(1, 1), Cells(myTotalRow, myTotalCol)).Select

End Sub

' This is the code to select the new range, but i believe that i nee
to make it return the values so i can insert it in the macro, or mayb
if i can call on it to achieve the range, but i do not know how to d
this.


here's the main code

Sub anmSum()
'
' anmSum Macro
'

'
Application.Run "personal.xls!SelectingRange"
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceDat
Source:=Sheets("announcement052704").Range("A1:Q63") _
, PlotBy:=xlColumns
' *** I need to change the sheet b/c the name will not be the same..
Also, the range should not be the same. ***
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
"Announcement Graph"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Announcement"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Tim
(min)"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Count"
End With
With ActiveChart
.HasAxis(xlCategory, xlPrimary) = True
.HasAxis(xlValue, xlPrimary) = True
End With
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType
xlCategoryScale
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
End Sub

if anyone knows how to solve this solution it would be great than
you.

tia

 

[Vasant Nanavati]

Dim rng As Range
Set rng = ActiveSheet.Range(Cells(1, 1), Cells(myTotalRow, myTotalCol))

Then use rng as your data source.