count shapes

[pit263]

I am trying to figure out how to tell vba to count all the shapes in a document. Afterwards i would like it to select the last (furthest to the right) shape and depending on what page it is, print that many pages.

If anyone could help me here, i would really appreciate it. I know it can't be to complicated but i just don't get it.

Thanks so much
Peter

[Bob Phillips]

ARe you talking about Excel or Word here?

[pit263]

about Excel

[Bob Phillips]

Well, the question made it sound like Word.

To clarify, you wa nt to find the last sheet with a shape on it, and then print all sheets up to that?

[pit263]

yeah, i know. As soon as you wrote it i realized.
Sorry about that.

"you wa nt to find the last sheet with a shape on it, and then print all sheets up to that?"
Yes exactly.

[Simon Lloyd]

Surely because of your statement you only need to check if a sheet has a shape then print the sheets that have one?

[Bob Phillips]

Maybe Simon, but I am reading it as all sheets upto last with a shape, regardless of whether they all have shapes?

[vba]

Dim sh As Worksheet
Dim arySheets() As String
Dim i As Long
Dim LastSheet As Long

With ActiveWorkbook

ReDim arySheets(1 To .Worksheets.Count)
For Each sh In .Worksheets

i = i + 1
arySheets(i) = sh.Name
If sh.Shapes.Count > 0 Then

LastSheet = i
End If
Next sh
If LastSheet > 0 Then

ReDim Preserve arySheets(1 To LastSheet)
Worksheets(arySheets).PrintPreview
End If
End With
[/vba]

[pit263]

yes, xld is right.
it doesn't matter whether there are shapes on each single page (even though there will be)
if the shape furthest right is on page five, for example, then i would like to print five pages

there are two little problems still though.
first, i would like to have it select sheet(2) and don't know where to write down sheets(2).select or activate
second, the program ends in the printpreview, exactly what i want. But(since there is other information on the page as well, such as writing in some cells and backround color) it wants to print more pages than i like it to.
On the bottom of my page it will say for example page 5/10 even though that is my last page. One way i could think of is telling excel to delete everything to the right of the last shape. But here again, not enough knowledge of VBA to even come up with a clue of how to do it.

Thanks so much again. I really appreciate the help

Peter

[Bob Phillips]

Wouldn't it be better for you to tidy up the worksheets, as a manual process? It should only be aonce-off.

What do you mean about selecting Sheet2? At what point?