[Excel] VBA for Distributing pictures vertically on separate pages

[eker676]

I have a need for a macro in excel that will take any number of pictures I paste into a worksheet, maximize their size for the printable page, and then put them all on different pages so when I print each picture will be allocated to one page. I'm trying it first with two shapes. I want each shape maximized for the page size, and put on two separate printable pages (in the same worksheet). The help files say to use the "Distribute" command, but I can't get it to work. I've tried it in Excel 2000 and 2003.

I know how to size the pictures, but can't get the Distribute command to work. With the attached code, I get a run time error and the message: "The specified value is out of range".

Here's the code right from the help files:

Sub Macro1()

Set mydocument = Worksheets(1)
With mydocument.Shapes
    numShapes = .Count
    If numShapes > 1 Then
        numAutoShapes = 0
        ReDim autoShpArray(1 To numShapes)
        For i = 1 To numShapes
            If .Item(i).Type = msoAutoShape Then
                numAutoShapes = numAutoShapes + 1
                autoShpArray(numAutoShapes) = .Item(i).Name
            End If
        Next
        If numAutoShapes > 1 Then
            ReDim Preserve autoShpArray(1 To numAutoShapes)
            Set asRange = .Range(autoShpArray)
            asRange.Distribute msoDistributeVertically, False
        End If
    End If
End With

End Sub

[westconn1]

if you can size all the pictures, you can also set the left and top position for each so that the next would have to be an a new page, though in excel pages can be horizantal as well as vertical
you can also move your hPageBreaks and VPageBreaks to suit

i did not get any error from running the sample code you posted, which line errored?

[eker676]

I got an error on this line:

asRange.Distribute msoDistributeVertically, False

It says "Runtime Error '-2147024809. The specified value is out of range"

Btw: This was ran on Excel 2000. What version of excel did you run it on?

[eker676]

Thanks WestConn1 for your reply.

I was able to get the distribute function to at least run without an error, when I found documentation that you have to have at least three shapes before the distribute function works.

It doesn't distribute the shapes very well though so I am still playing with it and playing with Page breaks and align etc. as you suggested.

More later, Thanks.