Resizing an Image using VBA

[Puppies72]

Hi all,

I use the macro below (cobbled together from recorded macros) to copy an image (generated from linked autoshapes), export it to a new workbook, attempt to resize it so that it exactly fills one page, and then save as...(will be saved as a pdf)

however the resize doesn't seem to be working can anyone highlight a better way to resize the image - it copies over as a 9 page image but I need it to be a single full page with no margins.

Many thanks - code below

Sub ExportMap()
Dim wb As Workbook
    Dim rng As Range
 
    Set rng = ActiveWorkbook.ActiveSheet.Range("A1:Z123")
    Set wb = Workbooks.Add
 
    rng.Copy
    wb.ActiveSheet.Pictures.Paste
    Application.CutCopyMode = False
    ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.Shapes.Range(Array("Picture 1")).Select
    Selection.ShapeRange.ScaleWidth 0.3434730056, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 0.375516335, msoFalse, msoScaleFromTopLeft
    Selection.ShapeRange.ScaleHeight 0.9974999475, msoFalse, msoScaleFromTopLeft
 
With Application.FileDialog(msoFileDialogSaveAs)
  .Title = "Choose name of file"
  .InitialFileName = "C:\Users\***\Desktop\"  
  .AllowMultiSelect = False
  If .Show = -1 Then
      .Execute
  Else
     MsgBox "User pressed cancel!"
  End If
End With
 
End Sub

 

[texasalynn]

take a look at this