ExcelBanter - Copy logo from sheet1 to new sheet3-10

If you know the name of the picture to copy, you can use code like:

' Copy the picture
Worksheets("Sheet1").Pictures("Picture1").CopyPict ure
' Paste it into the appropriate sheet and range
Worksheets("Sheet2").Range("C10").PasteSpecial
' Remove focus from the shape. Optional.
Worksheets("Sheet2").Range("C10").Activate

If you don't know the name of the picture, you can use the following
function to return the (first) picture whose top left cell is within a
specified Range.

Function PictureOfRange(RefCell As Range) As Excel.Picture
Dim P As Excel.Picture
Dim WS As Worksheet
Set WS = RefCell.Worksheet
For Each P In WS.Pictures
If Not Application.Intersect( _
P.TopLeftCell, RefCell) Is Nothing Then
Set PictureOfRange = P
Exit Function
End If
Next P
End Function

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

"Gregor" wrote in message
...
Hi,

I distribute a workbook that has two sheets in it and I use VBA macros
to pull data in and format it on 7 new sheets.

I need to copy the logo from sheet 1 to the new sheets.

I've selected the picture on sheet 1 and named it as Logo (just like
you would name a range), but when I call Range("Logo").copy, I get a
range error.

If I use Range("A1:).copy I get an empty string.

I've use Activesheet.paste to paste the image.

I know I'm missing something, but I don't know what. Any ideas?