Dynamic series range from AutoFilter
 

Hi All......

I have code below, that after I autofilter and copy results over to another
sheet, will go to AA9 and AE9 as the first cells in Series1 and Series2
sources...that works fine, except I never know where the bottom of the column
is going to be based on what data is filtered.....when I try to Record the
macro, I only get fixed references....AA9 and AE9 are fine for the top of the
column, but I need somehow to "find" the bottom of the column.......

Existing code:

Sub M034Chart()
'
' M034Chart Macro
' Macro recorded 04/12/2005 by CLR
'

'
Sheets("Reports").Select
Range("AA9:AA51,Ae9:Ae51").Select
Range("Ae9").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Reports").Range( _
"AA9:AA51,Ae9:Ae51"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Reports"
End Sub

Any help would be much appreciated....
Vaya con Dios,
Chuck, CABGx3

this might help
lastrow=cells(rows.count,"aa").end(xlup).row
myrng=Range("AA9:AA" & lastrow&",Ae9:Ae" & lastrow)

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Hi All......

I have code below, that after I autofilter and copy results over to
another
sheet, will go to AA9 and AE9 as the first cells in Series1 and Series2
sources...that works fine, except I never know where the bottom of the
column
is going to be based on what data is filtered.....when I try to Record the
macro, I only get fixed references....AA9 and AE9 are fine for the top of
the
column, but I need somehow to "find" the bottom of the column.......

Existing code:

Sub M034Chart()
'
' M034Chart Macro
' Macro recorded 04/12/2005 by CLR
'

'
Sheets("Reports").Select
Range("AA9:AA51,Ae9:Ae51").Select
Range("Ae9").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Reports").Range( _
"AA9:AA51,Ae9:Ae51"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Reports"
End Sub

Any help would be much appreciated....
Vaya con Dios,
Chuck, CABGx3

Thank you kind Sir.............I don't know exactly what to do with it, but
I will give it a go, or two or three <g.....

Vaya con Dios,
Chuck, CABGx3


"Don Guillett" wrote in message
...
this might help
lastrow=cells(rows.count,"aa").end(xlup).row
myrng=Range("AA9:AA" & lastrow&",Ae9:Ae" & lastrow)

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Hi All......

I have code below, that after I autofilter and copy results over to
another
sheet, will go to AA9 and AE9 as the first cells in Series1 and Series2
sources...that works fine, except I never know where the bottom of the
column
is going to be based on what data is filtered.....when I try to Record
the
macro, I only get fixed references....AA9 and AE9 are fine for the top
of
the
column, but I need somehow to "find" the bottom of the column.......

Existing code:

Sub M034Chart()
'
' M034Chart Macro
' Macro recorded 04/12/2005 by CLR
'

'
Sheets("Reports").Select
Range("AA9:AA51,Ae9:Ae51").Select
Range("Ae9").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Reports").Range( _
"AA9:AA51,Ae9:Ae51"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Reports"
End Sub

Any help would be much appreciated....
Vaya con Dios,
Chuck, CABGx3

try

Sub dochart()
lastrow = Cells(Rows.Count, "aa").End(xlUp).Row
Set myrng = Range("AA9:AA" & lastrow & ",AE:AE" & lastrow)
Charts.Add
With ActiveChart
.ChartType = xlLineMarkers
.SetSourceData Source:=myrng, PlotBy:=xlColumns
.Location Whe=xlLocationAsObject, Name:="Reports"
End With
Range("aa9").Select
End Sub

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Thank you kind Sir.............I don't know exactly what to do with it,
but
I will give it a go, or two or three <g.....

Vaya con Dios,
Chuck, CABGx3


"Don Guillett" wrote in message
...
this might help
lastrow=cells(rows.count,"aa").end(xlup).row
myrng=Range("AA9:AA" & lastrow&",Ae9:Ae" & lastrow)

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Hi All......

I have code below, that after I autofilter and copy results over to
another
sheet, will go to AA9 and AE9 as the first cells in Series1 and
Series2
sources...that works fine, except I never know where the bottom of the
column
is going to be based on what data is filtered.....when I try to Record
the
macro, I only get fixed references....AA9 and AE9 are fine for the top
of
the
column, but I need somehow to "find" the bottom of the column.......

Existing code:

Sub M034Chart()
'
' M034Chart Macro
' Macro recorded 04/12/2005 by CLR
'

'
Sheets("Reports").Select
Range("AA9:AA51,Ae9:Ae51").Select
Range("Ae9").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Reports").Range( _
"AA9:AA51,Ae9:Ae51"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Reports"
End Sub

Any help would be much appreciated....
Vaya con Dios,
Chuck, CABGx3

Thanks Don.......

I'm off today, car wouldn't start, then found I need to take my Lady to the
Doctor and one of the cats to the Vet, etc etc........but will try it
tomorrow at work for sure........

Many thanks,
Vaya con Dios,
Chuck, CABGx3


"Don Guillett" wrote in message
...
try

Sub dochart()
lastrow = Cells(Rows.Count, "aa").End(xlUp).Row
Set myrng = Range("AA9:AA" & lastrow & ",AE:AE" & lastrow)
Charts.Add
With ActiveChart
.ChartType = xlLineMarkers
.SetSourceData Source:=myrng, PlotBy:=xlColumns
.Location Whe=xlLocationAsObject, Name:="Reports"
End With
Range("aa9").Select
End Sub

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Thank you kind Sir.............I don't know exactly what to do with it,
but
I will give it a go, or two or three <g.....

Vaya con Dios,
Chuck, CABGx3


"Don Guillett" wrote in message
...
this might help
lastrow=cells(rows.count,"aa").end(xlup).row
myrng=Range("AA9:AA" & lastrow&",Ae9:Ae" & lastrow)

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Hi All......

I have code below, that after I autofilter and copy results over to
another
sheet, will go to AA9 and AE9 as the first cells in Series1 and
Series2
sources...that works fine, except I never know where the bottom of
the
column
is going to be based on what data is filtered.....when I try to
Record
the
macro, I only get fixed references....AA9 and AE9 are fine for the
top
of
the
column, but I need somehow to "find" the bottom of the column.......

Existing code:

Sub M034Chart()
'
' M034Chart Macro
' Macro recorded 04/12/2005 by CLR
'

'
Sheets("Reports").Select
Range("AA9:AA51,Ae9:Ae51").Select
Range("Ae9").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Reports").Range( _
"AA9:AA51,Ae9:Ae51"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject, Name:="Reports"
End Sub

Any help would be much appreciated....
Vaya con Dios,
Chuck, CABGx3

let me know how it works out. Enjoy your "day off"

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Thanks Don.......

I'm off today, car wouldn't start, then found I need to take my Lady to
the
Doctor and one of the cats to the Vet, etc etc........but will try it
tomorrow at work for sure........

Many thanks,
Vaya con Dios,
Chuck, CABGx3


"Don Guillett" wrote in message
...
try

Sub dochart()
lastrow = Cells(Rows.Count, "aa").End(xlUp).Row
Set myrng = Range("AA9:AA" & lastrow & ",AE:AE" & lastrow)
Charts.Add
With ActiveChart
.ChartType = xlLineMarkers
.SetSourceData Source:=myrng, PlotBy:=xlColumns
.Location Whe=xlLocationAsObject, Name:="Reports"
End With
Range("aa9").Select
End Sub

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Thank you kind Sir.............I don't know exactly what to do with
it,
but
I will give it a go, or two or three <g.....

Vaya con Dios,
Chuck, CABGx3


"Don Guillett" wrote in message
...
this might help
lastrow=cells(rows.count,"aa").end(xlup).row
myrng=Range("AA9:AA" & lastrow&",Ae9:Ae" & lastrow)

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Hi All......

I have code below, that after I autofilter and copy results over
to
another
sheet, will go to AA9 and AE9 as the first cells in Series1 and
Series2
sources...that works fine, except I never know where the bottom of
the
column
is going to be based on what data is filtered.....when I try to
Record
the
macro, I only get fixed references....AA9 and AE9 are fine for the
top
of
the
column, but I need somehow to "find" the bottom of the
column.......

Existing code:

Sub M034Chart()
'
' M034Chart Macro
' Macro recorded 04/12/2005 by CLR
'

'
Sheets("Reports").Select
Range("AA9:AA51,Ae9:Ae51").Select
Range("Ae9").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Reports").Range( _
"AA9:AA51,Ae9:Ae51"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject,
Name:="Reports"
End Sub

Any help would be much appreciated....
Vaya con Dios,
Chuck, CABGx3

Many many thanks Don........

A little tweaking to adapt to my particular environment and your code flies
perfectly. It does just what I asked for....it's don't get much better than
that.!

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

let me know how it works out. Enjoy your "day off"

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Thanks Don.......

I'm off today, car wouldn't start, then found I need to take my Lady to
the
Doctor and one of the cats to the Vet, etc etc........but will try it
tomorrow at work for sure........

Many thanks,
Vaya con Dios,
Chuck, CABGx3


"Don Guillett" wrote in message
...
try

Sub dochart()
lastrow = Cells(Rows.Count, "aa").End(xlUp).Row
Set myrng = Range("AA9:AA" & lastrow & ",AE:AE" & lastrow)
Charts.Add
With ActiveChart
.ChartType = xlLineMarkers
.SetSourceData Source:=myrng, PlotBy:=xlColumns
.Location Whe=xlLocationAsObject, Name:="Reports"
End With
Range("aa9").Select
End Sub

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Thank you kind Sir.............I don't know exactly what to do with
it,
but
I will give it a go, or two or three <g.....

Vaya con Dios,
Chuck, CABGx3


"Don Guillett" wrote in message
...
this might help
lastrow=cells(rows.count,"aa").end(xlup).row
myrng=Range("AA9:AA" & lastrow&",Ae9:Ae" & lastrow)

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Hi All......

I have code below, that after I autofilter and copy results over
to
another
sheet, will go to AA9 and AE9 as the first cells in Series1 and
Series2
sources...that works fine, except I never know where the bottom of
the
column
is going to be based on what data is filtered.....when I try to
Record
the
macro, I only get fixed references....AA9 and AE9 are fine for the
top
of
the
column, but I need somehow to "find" the bottom of the
column.......

Existing code:

Sub M034Chart()
'
' M034Chart Macro
' Macro recorded 04/12/2005 by CLR
'

'
Sheets("Reports").Select
Range("AA9:AA51,Ae9:Ae51").Select
Range("Ae9").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Reports").Range( _
"AA9:AA51,Ae9:Ae51"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject,
Name:="Reports"
End Sub

Any help would be much appreciated....
Vaya con Dios,
Chuck, CABGx3

glad it helped

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Many many thanks Don........

A little tweaking to adapt to my particular environment and your code
flies
perfectly. It does just what I asked for....it's don't get much better
than
that.!

Thanks again,
Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

let me know how it works out. Enjoy your "day off"

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Thanks Don.......

I'm off today, car wouldn't start, then found I need to take my Lady
to
the
Doctor and one of the cats to the Vet, etc etc........but will try it
tomorrow at work for sure........

Many thanks,
Vaya con Dios,
Chuck, CABGx3


"Don Guillett" wrote in message
...
try

Sub dochart()
lastrow = Cells(Rows.Count, "aa").End(xlUp).Row
Set myrng = Range("AA9:AA" & lastrow & ",AE:AE" & lastrow)
Charts.Add
With ActiveChart
.ChartType = xlLineMarkers
.SetSourceData Source:=myrng, PlotBy:=xlColumns
.Location Whe=xlLocationAsObject, Name:="Reports"
End With
Range("aa9").Select
End Sub

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Thank you kind Sir.............I don't know exactly what to do
with
it,
but
I will give it a go, or two or three <g.....

Vaya con Dios,
Chuck, CABGx3


"Don Guillett" wrote in message
...
this might help
lastrow=cells(rows.count,"aa").end(xlup).row
myrng=Range("AA9:AA" & lastrow&",Ae9:Ae" & lastrow)

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Hi All......

I have code below, that after I autofilter and copy results
over
to
another
sheet, will go to AA9 and AE9 as the first cells in Series1
and
Series2
sources...that works fine, except I never know where the
bottom of
the
column
is going to be based on what data is filtered.....when I try
to
Record
the
macro, I only get fixed references....AA9 and AE9 are fine for
the
top
of
the
column, but I need somehow to "find" the bottom of the
column.......

Existing code:

Sub M034Chart()
'
' M034Chart Macro
' Macro recorded 04/12/2005 by CLR
'

'
Sheets("Reports").Select
Range("AA9:AA51,Ae9:Ae51").Select
Range("Ae9").Activate
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData
Source:=Sheets("Reports").Range( _
"AA9:AA51,Ae9:Ae51"), PlotBy:=xlColumns
ActiveChart.Location Whe=xlLocationAsObject,
Name:="Reports"
End Sub

Any help would be much appreciated....
Vaya con Dios,
Chuck, CABGx3