VBA print code to select range + pasted chart 
Author Message
 VBA print code to select range + pasted chart

Sorry to keep posting this= I have VBA that selects a
FILTERED range and prints it, but how do I get it to select
the filtered range and either (1) a chart pasted in the
empty rows below the range and/or (2) add 20 empty rows
below last row of the Dbase and combine with the selected
filtered range to create the print range.. I'll just paste
the chart in the 20 row space. Tom Ogilvy suggested a VBA
but I couldn't get it to recognize the chart.

Tom: I tried to incorporate the set rng code in my print
range macro, but it
won't recognize the chart object. The chart is VBA placed by
another macro and
gets a progressive name such as "chart13962_Click", then
13963, and so on.
Following is the chart macro.... should Set Rng VBA go in
here, and if so, will
the PRINTSELECTEDAREA macro pick it up? thanx TOM

Sub aaMakeChart1()
' aaMakeChart1 Macro recorded 9/8/2000 by TCS
' Select NAME DateRange (XVALS)
    ActiveCell.Offset(1, 0).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Name = "xval"
' Select NAME ValuesRange (YVALS)
    ActiveCell.Offset(1, 0).Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Name = "yval"
    ActiveChart.ChartType = xl3DColumnClustered
    ActiveChart.SetSourceData _
    Source:=Sheets("TABLE1").Range("xval,yval"), _
    ActiveChart.Location Where:=xlLocationAsObject,
    With ActiveChart
        .HasAxis(xlCategory) = True
        .HasAxis(xlValue) = True
    End With
    ActiveChart.Axes(xlCategory).CategoryType =
    ActiveChart.HasLegend = False
'Paste chart= GLOBAL FAILED
    Set rng = Cells(Rows.Count, "A").End(xlUp)(2)
    ActiveSheet.ChartObjects("chart1").Top = rng.Top
    ActiveSheet.ChartObjects("chart1").Left = rng.Left
End Sub


> I don't think hidden rows are an issue.  You need to put
the chart on the
> next row after your data - regardless of which rows are
hidden, your chart
> will always start on this row.>
> set rng = Cells(rows.count,"A").end(xlup)(2)>
> ActiveSheet.Chartobjects("Chart1").top = rng.top
> Activesheet.Chartobjects("Chart1").Left = rng.left
>> to modify you existing code:
>> '4=Name LowRight Corner= "DataLastCell"
>     selection.Resize(selection.rows.count+20,).Select
>     Selection.Name = "DataLastCell"

> '5=Select Entire Range for Printing
>     Range("A3:datalastcell").Select
> '6=Printout Selection after Preview

> Regards,
> Tom Ogilvy
> MVP Excel

> I have a large database that filters & returns rows of

selected data. After

- Show quoted text -

> the last filtered row, I want to insert a chart that takes
up another 20
> rows, placed below the last row (usually invisible) of the
database. Could
> someone give me code to select the visible, filtered rows
and add another 20
> rows to encompass the chart I paste in.
> My current print selection formula for the autofiltered
portion is...
> Sub PrintSelectedAreaTOM()
> '7/10/2000 by TCS
> '1=Identify Top Left of Range
>     Range("A3").Select
> '2=Then go to Lowest Right Corner of Visible Range
>     Selection.End(xlDown).Select
>     ActiveCell.Offset(0, 20).Range("A1").Select
> '3=Identify Lowest Right Corner of Visible Range
> '4=Name LowRight Corner= "DataLastCell"
>     Selection.Name = "DataLastCell"
> '5=Select Entire Range for Printing
>     Range("A3:datalastcell").Select
> '6=Printout Selection after Preview
>     Selection.PrintOut Copies:=1, Preview:=True,
>     ActiveWindow.ScrollColumn = 1
>     Range("A3").Select
> End Sub
> What code do I modify to add the other 20 rows that

includes the chart? It
> would be great if the code detected the chart and

automatically added it to
> print area. (If I offset the last cell by 20 rows, it

selects the invisible
> rows, not the chart rows at end of database.) NOTE:
database length is
> dynamic.
> Following are similar replies from recent newsgroup. Would
any of these
> help?
> ..from 6/4 post
> Range("B1", RealLastCell(ActiveSheet)).Select
> The above is a slightly modified version of a custom

function found on John

- Show quoted text -

> Walkenbach's web site.  It works properly when selecting
the area I wish to
> set as the Print Area. What I need to know is how do I use
the selected area
> as the Print Area??  I have tried the following, but get
> ActiveSheet.PageSetup.PrintArea = ActiveSheet.Selection
>       ..the answer
> After the command you have, you could use this:
> ActiveSheet.PageSetup.PrintArea = Selection.Address
> Or you could do both in the same command:
> ActiveSheet.PageSetup.PrintArea = _
>    Range("B1", RealLastCell(ActiveSheet)).Address
> If you want a relative (not absolute) address, use the
> optional parameters for the Address property:
> ActiveSheet.PageSetup.PrintArea = _
>    Selection.Address(RowAbsolute:=False,


- Show quoted text -


> ..Here's another that hides blank rows ...
> This works by first hiding unwanted blank rows, printing
what remains, and
> then restoring the sheet as before.
> Sub PritntSheet()
> Application.ScreenUpdating = False
> For i = 1 To 2000
>     If Cells(i, 1) = "" Then Rows(i).Hidden = True  'check
first column for
> something
> Next i
> ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1,
> Rows("1:2000").Hidden = False
> End Sub
> ..Would this one from Tom Ogilvy work if xlLastCell

detects inserted chart,

- Show quoted text -

> or if I can add 20 rows to it...
> Sub Tester2()
> ActiveSheet.PageSetup.PrintArea = _
>  Range(Range("A2"), Range("A2").SpecialCells(xlLastCell)).
_   ' add chart
> or 20+ rows
>   Address
> End Sub
> Thanx as always for this great site! TOM
> (fit on 1 page for Tom)
> force the width of a spreadsheet to fit to one page:
> With Worksheets("Sheet1").PageSetup
>     .Zoom = False
>     .FitToPagesWide = 1
> End With

Wed, 18 Jun 1902 08:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Print Code to Select Filtered Cells + Pasted Chart

2. Using VBA to select a highlighted range and paste values to another workbook

3. vba-select multiple disconnected ranges - to copy paste special values in place

4. Selecting a range of cells and charting with VBA

5. VBA code of moving down selected range

6. Need help Comparing two selected ranges of same dimension in twoworkbooks - VBA code

7. New to vba - print range select

8. How to get macro to select range for chart - range varies

9. VBA :Selecting ranges that vary, Naming ranges based on Formatting

10. How do I select a constant time range from a variable time range using VBA

Powered by phpBB® Forum Software © phpBB Group