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

Then use rng as your data source.

--

Vasant



"Vapanda " wrote in message
...
Is it possible where i can create a macro that automates graphs. The
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 the
data... I tried to use another code to modify this but i am having no
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 need
to make it return the values so i can insert it in the macro, or maybe
if i can call on it to achieve the range, but i do not know how to do
this.


here's the main code

Sub anmSum()
'
' anmSum Macro
'

'
Application.Run "personal.xls!SelectingRange"
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
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 Whe=xlLocationAsNewSheet, Name:= _
"Announcement Graph"
With ActiveChart
HasTitle = True
ChartTitle.Characters.Text = "Announcement"
Axes(xlCategory, xlPrimary).HasTitle = True
Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Time
(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 thank
you.

tia.


---
Message posted from http://www.ExcelForum.com/