Hi all

while copying data, I want to copy images between sheets. How can I do it?


Clara

thank you so much for your help

Hi Clara,

It's not clear if you want to copy individual pictures or all pictures from
one sheet to another. Here's a pair of macros to do both -

Sub CopyAllPictures()
Dim r As Long, c As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim pic As Picture

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

r = wsSource.Rows.Count
c = wsSource.Columns.Count

For Each pic In wsSource.Pictures
With pic.TopLeftCell
If .Row < r Then r = .Row
If .Column < c Then c = .Column
End With
Next

wsDest.Activate
wsDest.Cells(r, c).Activate

wsSource.Pictures.Copy
wsDest.Paste

wsDest.Cells(r, c).Activate

End Sub

Sub CopyOnePicture()
Dim wsSource As Worksheet
Dim wsDest As Worksheet

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

Set pic = wsSource.Pictures("Picture 1")

pic.Copy
wsDest.Paste

With wsDest.Pictures(wsDest.Pictures.Count)
.Left = pic.Left
.Top = pic.Top
End With

End Sub

Regards,
Peter T

"clara" wrote in message
...
Hi all

while copying data, I want to copy images between sheets. How can I do it?


Clara

thank you so much for your help

Hi Peter,

Thanks for your code, unfortunately, I am using Excel 2000 and I can not
find Pictures property of object Worksheet.

Clara
--
thank you so much for your help


"Peter T" wrote:

Hi Clara,

It's not clear if you want to copy individual pictures or all pictures from
one sheet to another. Here's a pair of macros to do both -

Sub CopyAllPictures()
Dim r As Long, c As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim pic As Picture

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

r = wsSource.Rows.Count
c = wsSource.Columns.Count

For Each pic In wsSource.Pictures
With pic.TopLeftCell
If .Row < r Then r = .Row
If .Column < c Then c = .Column
End With
Next

wsDest.Activate
wsDest.Cells(r, c).Activate

wsSource.Pictures.Copy
wsDest.Paste

wsDest.Cells(r, c).Activate

End Sub

Sub CopyOnePicture()
Dim wsSource As Worksheet
Dim wsDest As Worksheet

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

Set pic = wsSource.Pictures("Picture 1")

pic.Copy
wsDest.Paste

With wsDest.Pictures(wsDest.Pictures.Count)
.Left = pic.Left
.Top = pic.Top
End With

End Sub

Regards,
Peter T

"clara" wrote in message
...
Hi all

while copying data, I want to copy images between sheets. How can I do it?


Clara

thank you so much for your help

Picture and Pictures is a 'hidden' object and collection at the
DrawingObject level, should work fine in xl2000.

Type don't paste -
Dim pic as picture
After pressing Enter picture will revert to Picture

In a panel object browser F2 right click and select 'Show Hidden Members'

Did you try the macros.

Regards,
Peter T

"clara" wrote in message
...
Hi Peter,

Thanks for your code, unfortunately, I am using Excel 2000 and I can not
find Pictures property of object Worksheet.

Clara
--
thank you so much for your help


"Peter T" wrote:

Hi Clara,

It's not clear if you want to copy individual pictures or all pictures
from
one sheet to another. Here's a pair of macros to do both -

Sub CopyAllPictures()
Dim r As Long, c As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim pic As Picture

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

r = wsSource.Rows.Count
c = wsSource.Columns.Count

For Each pic In wsSource.Pictures
With pic.TopLeftCell
If .Row < r Then r = .Row
If .Column < c Then c = .Column
End With
Next

wsDest.Activate
wsDest.Cells(r, c).Activate

wsSource.Pictures.Copy
wsDest.Paste

wsDest.Cells(r, c).Activate

End Sub

Sub CopyOnePicture()
Dim wsSource As Worksheet
Dim wsDest As Worksheet

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

Set pic = wsSource.Pictures("Picture 1")

pic.Copy
wsDest.Paste

With wsDest.Pictures(wsDest.Pictures.Count)
.Left = pic.Left
.Top = pic.Top
End With

End Sub

Regards,
Peter T

"clara" wrote in message
...
Hi all

while copying data, I want to copy images between sheets. How can I do
it?


Clara

thank you so much for your help

Hi Peter,

Your code works very well. But I still have two questions to ask:

1.why do you not use the DrawingObject bridge the Worksheet object and
Pictures collection
2.How can I chage the picure objec in Design Mode, I mean I can view the
property of the object like a button or a worksheet.

Thank you very much

Clara
--
thank you so much for your help


"Peter T" wrote:

Picture and Pictures is a 'hidden' object and collection at the
DrawingObject level, should work fine in xl2000.

Type don't paste -
Dim pic as picture
After pressing Enter picture will revert to Picture

In a panel object browser F2 right click and select 'Show Hidden Members'

Did you try the macros.

Regards,
Peter T

"clara" wrote in message
...
Hi Peter,

Thanks for your code, unfortunately, I am using Excel 2000 and I can not
find Pictures property of object Worksheet.

Clara
--
thank you so much for your help


"Peter T" wrote:

Hi Clara,

It's not clear if you want to copy individual pictures or all pictures
from
one sheet to another. Here's a pair of macros to do both -

Sub CopyAllPictures()
Dim r As Long, c As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim pic As Picture

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

r = wsSource.Rows.Count
c = wsSource.Columns.Count

For Each pic In wsSource.Pictures
With pic.TopLeftCell
If .Row < r Then r = .Row
If .Column < c Then c = .Column
End With
Next

wsDest.Activate
wsDest.Cells(r, c).Activate

wsSource.Pictures.Copy
wsDest.Paste

wsDest.Cells(r, c).Activate

End Sub

Sub CopyOnePicture()
Dim wsSource As Worksheet
Dim wsDest As Worksheet

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

Set pic = wsSource.Pictures("Picture 1")

pic.Copy
wsDest.Paste

With wsDest.Pictures(wsDest.Pictures.Count)
.Left = pic.Left
.Top = pic.Top
End With

End Sub

Regards,
Peter T

"clara" wrote in message
...
Hi all

while copying data, I want to copy images between sheets. How can I do
it?


Clara

thank you so much for your help

1. If you only want to copy the one Picture you could equally use
DrawingObject or Picture object types, no difference assuming of course you
want to deal with a Picture. If you are doing something with multiple
Pictures I find it much easier to use the Pictures collection. It kind of
acts like a filter to the DrawingObjects to return only Pictures. In the
example what could be easier than -

wsSource.Pictures.Copy

vs - all the following to do same thing -

Dim shp As Shape
Dim i As Long

if wsSource.shapes.count = 0 then exit sub
ReDim arr(1 To wsSource.Shapes.Count)

For Each shp In wsSource.Shapes
If shp.Type = msoPicture Then
i = i + 1
arr(i) = shp.DrawingObject.ZOrder
End If
Next
if i then
ReDim Preserve arr(1 To i)
wsSource.DrawingObjects(arr).Copy
else
' no pictures
end if

2. I don't quite follow what you are asking. If you declare pic as Picture,
then type pic. you should see the intellisence as soon as you type the dot.

Set pic = ActiveSheet.Pictures(1)
pic.Duplicate
With pic
..Left = .Left + .Width
End With

You mentioned buttons, similarly Button and Buttons are hidden subset of the
DrawingObjects collection
Dim but as Button
for each but in Activesheet.Buttons
debug.? but.caption

Regards,
Peter T


"clara" wrote in message
...
Hi Peter,

Your code works very well. But I still have two questions to ask:

1.why do you not use the DrawingObject bridge the Worksheet object and
Pictures collection
2.How can I chage the picure objec in Design Mode, I mean I can view the
property of the object like a button or a worksheet.

Thank you very much

Clara
--
thank you so much for your help


"Peter T" wrote:

Picture and Pictures is a 'hidden' object and collection at the
DrawingObject level, should work fine in xl2000.

Type don't paste -
Dim pic as picture
After pressing Enter picture will revert to Picture

In a panel object browser F2 right click and select 'Show Hidden
Members'

Did you try the macros.

Regards,
Peter T

"clara" wrote in message
...
Hi Peter,

Thanks for your code, unfortunately, I am using Excel 2000 and I can
not
find Pictures property of object Worksheet.

Clara
--
thank you so much for your help


"Peter T" wrote:

Hi Clara,

It's not clear if you want to copy individual pictures or all
pictures
from
one sheet to another. Here's a pair of macros to do both -

Sub CopyAllPictures()
Dim r As Long, c As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim pic As Picture

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

r = wsSource.Rows.Count
c = wsSource.Columns.Count

For Each pic In wsSource.Pictures
With pic.TopLeftCell
If .Row < r Then r = .Row
If .Column < c Then c = .Column
End With
Next

wsDest.Activate
wsDest.Cells(r, c).Activate

wsSource.Pictures.Copy
wsDest.Paste

wsDest.Cells(r, c).Activate

End Sub

Sub CopyOnePicture()
Dim wsSource As Worksheet
Dim wsDest As Worksheet

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

Set pic = wsSource.Pictures("Picture 1")

pic.Copy
wsDest.Paste

With wsDest.Pictures(wsDest.Pictures.Count)
.Left = pic.Left
.Top = pic.Top
End With

End Sub

Regards,
Peter T

"clara" wrote in message
...
Hi all

while copying data, I want to copy images between sheets. How can
I do
it?


Clara

thank you so much for your help

I have a similar request to that posed in this thread and I have used the
code identified below to copy all pictures.

However, I am getting a Type Mismatch error when running it. On debug, the
system highlights the word "Next".

Am I missing something?

Thanks
Sarah

"Peter T" wrote:

Hi Clara,

It's not clear if you want to copy individual pictures or all pictures from
one sheet to another. Here's a pair of macros to do both -

Sub CopyAllPictures()
Dim r As Long, c As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim pic As Picture

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

r = wsSource.Rows.Count
c = wsSource.Columns.Count

For Each pic In wsSource.Pictures
With pic.TopLeftCell
If .Row < r Then r = .Row
If .Column < c Then c = .Column
End With
Next

wsDest.Activate
wsDest.Cells(r, c).Activate

wsSource.Pictures.Copy
wsDest.Paste

wsDest.Cells(r, c).Activate

End Sub

Sub CopyOnePicture()
Dim wsSource As Worksheet
Dim wsDest As Worksheet

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

Set pic = wsSource.Pictures("Picture 1")

pic.Copy
wsDest.Paste

With wsDest.Pictures(wsDest.Pictures.Count)
.Left = pic.Left
.Top = pic.Top
End With

End Sub

Regards,
Peter T

"clara" wrote in message
...
Hi all

while copying data, I want to copy images between sheets. How can I do it?


Clara

thank you so much for your help

Either you changed the code (it worked fine for me) or something bad got
included when you did the copy|paste.

If you changed the code, post your current version.

If you pasted the code, then try deleting that line and just retype that "next"
line.

(Sometimes, copying from a window on the web can get you some extra characters.)

Sarah (OGI) wrote:

I have a similar request to that posed in this thread and I have used the
code identified below to copy all pictures.

However, I am getting a Type Mismatch error when running it. On debug, the
system highlights the word "Next".

Am I missing something?

Thanks
Sarah

"Peter T" wrote:

Hi Clara,

It's not clear if you want to copy individual pictures or all pictures from
one sheet to another. Here's a pair of macros to do both -

Sub CopyAllPictures()
Dim r As Long, c As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim pic As Picture

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

r = wsSource.Rows.Count
c = wsSource.Columns.Count

For Each pic In wsSource.Pictures
With pic.TopLeftCell
If .Row < r Then r = .Row
If .Column < c Then c = .Column
End With
Next

wsDest.Activate
wsDest.Cells(r, c).Activate

wsSource.Pictures.Copy
wsDest.Paste

wsDest.Cells(r, c).Activate

End Sub

Sub CopyOnePicture()
Dim wsSource As Worksheet
Dim wsDest As Worksheet

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

Set pic = wsSource.Pictures("Picture 1")

pic.Copy
wsDest.Paste

With wsDest.Pictures(wsDest.Pictures.Count)
.Left = pic.Left
.Top = pic.Top
End With

End Sub

Regards,
Peter T

"clara" wrote in message
...
Hi all

while copying data, I want to copy images between sheets. How can I do it?


Clara

thank you so much for your help




--

Dave Peterson

Dave

I've tried using this code through a copy/paste, and also by typing it out
from scratch, but both ways result in a run-time error 13, Type Mismatch.
The code being used should match what had previously been suggested:

==========
Sub CopyAllPictures()

Dim r As Long, c As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim pic As Picture

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

r = wsSource.Rows.Count
c = wsSource.Columns.Count

For Each pic In wsSource.Pictures
With pic.TopLeftCell
If .Row < r Then r = .Row
If .Column < c Then c = .Column
End With
Next

wsDest.Activate
wsDest.Cells(r, c).Activate

wsSource.Pictures.Copy
wsDest.Paste

wsDest.Cells(r, c).Activate

End Sub
=======

Not sure why its not working?

"Dave Peterson" wrote:

Either you changed the code (it worked fine for me) or something bad got
included when you did the copy|paste.

If you changed the code, post your current version.

If you pasted the code, then try deleting that line and just retype that "next"
line.

(Sometimes, copying from a window on the web can get you some extra characters.)

Sarah (OGI) wrote:

I have a similar request to that posed in this thread and I have used the
code identified below to copy all pictures.

However, I am getting a Type Mismatch error when running it. On debug, the
system highlights the word "Next".

Am I missing something?

Thanks
Sarah

"Peter T" wrote:

Hi Clara,

It's not clear if you want to copy individual pictures or all pictures from
one sheet to another. Here's a pair of macros to do both -

Sub CopyAllPictures()
Dim r As Long, c As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim pic As Picture

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

r = wsSource.Rows.Count
c = wsSource.Columns.Count

For Each pic In wsSource.Pictures
With pic.TopLeftCell
If .Row < r Then r = .Row
If .Column < c Then c = .Column
End With
Next

wsDest.Activate
wsDest.Cells(r, c).Activate

wsSource.Pictures.Copy
wsDest.Paste

wsDest.Cells(r, c).Activate

End Sub

Sub CopyOnePicture()
Dim wsSource As Worksheet
Dim wsDest As Worksheet

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

Set pic = wsSource.Pictures("Picture 1")

pic.Copy
wsDest.Paste

With wsDest.Pictures(wsDest.Pictures.Count)
.Left = pic.Left
.Top = pic.Top
End With

End Sub

Regards,
Peter T

"clara" wrote in message
...
Hi all

while copying data, I want to copy images between sheets. How can I do it?


Clara

thank you so much for your help




--

Dave Peterson

Your code worked fine for me.

What line caused the mismatch error?

Sarah (OGI) wrote:

Dave

I've tried using this code through a copy/paste, and also by typing it out
from scratch, but both ways result in a run-time error 13, Type Mismatch.
The code being used should match what had previously been suggested:

==========
Sub CopyAllPictures()

Dim r As Long, c As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim pic As Picture

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

r = wsSource.Rows.Count
c = wsSource.Columns.Count

For Each pic In wsSource.Pictures
With pic.TopLeftCell
If .Row < r Then r = .Row
If .Column < c Then c = .Column
End With
Next

wsDest.Activate
wsDest.Cells(r, c).Activate

wsSource.Pictures.Copy
wsDest.Paste

wsDest.Cells(r, c).Activate

End Sub
=======

Not sure why its not working?

"Dave Peterson" wrote:

Either you changed the code (it worked fine for me) or something bad got
included when you did the copy|paste.

If you changed the code, post your current version.

If you pasted the code, then try deleting that line and just retype that "next"
line.

(Sometimes, copying from a window on the web can get you some extra characters.)

Sarah (OGI) wrote:

I have a similar request to that posed in this thread and I have used the
code identified below to copy all pictures.

However, I am getting a Type Mismatch error when running it. On debug, the
system highlights the word "Next".

Am I missing something?

Thanks
Sarah

"Peter T" wrote:

Hi Clara,

It's not clear if you want to copy individual pictures or all pictures from
one sheet to another. Here's a pair of macros to do both -

Sub CopyAllPictures()
Dim r As Long, c As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim pic As Picture

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

r = wsSource.Rows.Count
c = wsSource.Columns.Count

For Each pic In wsSource.Pictures
With pic.TopLeftCell
If .Row < r Then r = .Row
If .Column < c Then c = .Column
End With
Next

wsDest.Activate
wsDest.Cells(r, c).Activate

wsSource.Pictures.Copy
wsDest.Paste

wsDest.Cells(r, c).Activate

End Sub

Sub CopyOnePicture()
Dim wsSource As Worksheet
Dim wsDest As Worksheet

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

Set pic = wsSource.Pictures("Picture 1")

pic.Copy
wsDest.Paste

With wsDest.Pictures(wsDest.Pictures.Count)
.Left = pic.Left
.Top = pic.Top
End With

End Sub

Regards,
Peter T

"clara" wrote in message
...
Hi all

while copying data, I want to copy images between sheets. How can I do it?


Clara

thank you so much for your help




--

Dave Peterson


--

Dave Peterson

There doesn't appear to be anything wrong with the code. In your OP you say
the code breaks on the "Next" line (odd). Try commenting all the code except
this

Set wsSource = Worksheets("Sheet1") ' if necessary also qualify with its
workbook

For Each pic In wsSource.Pictures
With pic.TopLeftCell
If .Row < r Then r = .Row
If .Column < c Then c = .Column
End With
Next

msgbox r & " " & c

From your later post, if I follow, you want to loop multiple sheets copying
pictures from looped sheet to another. If you want to use CopyAllPictures in
the loop adapt the two sheet declarations as arguments

Sub CopyAllPictures(wsSource As Worksheet, wsDest As Worksheet)

In your loop, call CopyAllPictures and pass the appropriate source &
destination sheets.

The routine needs to activate the destination sheet in order to ensure the
corresponding top-left cell as relates to ALL the pictures is activated
before doing the Paste. That's to ensure they all get pasted to the right
location in one go. Although not essential, the final step is to deselect
the pasted pictures by selecting a cell, again that requires the destination
sheet to be active. So you will probably want to disable screen updating in
your main routine. Also, you might need to reactivate the sheet that was
originally active (not sure I didn't look at what you are doing)

Regards,
Peter T


"Sarah (OGI)" wrote in message
...
Dave

I've tried using this code through a copy/paste, and also by typing it out
from scratch, but both ways result in a run-time error 13, Type Mismatch.
The code being used should match what had previously been suggested:

==========
Sub CopyAllPictures()

Dim r As Long, c As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim pic As Picture

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

r = wsSource.Rows.Count
c = wsSource.Columns.Count

For Each pic In wsSource.Pictures
With pic.TopLeftCell
If .Row < r Then r = .Row
If .Column < c Then c = .Column
End With
Next

wsDest.Activate
wsDest.Cells(r, c).Activate

wsSource.Pictures.Copy
wsDest.Paste

wsDest.Cells(r, c).Activate

End Sub
=======

Not sure why its not working?

"Dave Peterson" wrote:

Either you changed the code (it worked fine for me) or something bad got
included when you did the copy|paste.

If you changed the code, post your current version.

If you pasted the code, then try deleting that line and just retype that
"next"
line.

(Sometimes, copying from a window on the web can get you some extra
characters.)

Sarah (OGI) wrote:

I have a similar request to that posed in this thread and I have used
the
code identified below to copy all pictures.

However, I am getting a Type Mismatch error when running it. On
debug, the
system highlights the word "Next".

Am I missing something?

Thanks
Sarah

"Peter T" wrote:

Hi Clara,

It's not clear if you want to copy individual pictures or all
pictures from
one sheet to another. Here's a pair of macros to do both -

Sub CopyAllPictures()
Dim r As Long, c As Long
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim pic As Picture

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

r = wsSource.Rows.Count
c = wsSource.Columns.Count

For Each pic In wsSource.Pictures
With pic.TopLeftCell
If .Row < r Then r = .Row
If .Column < c Then c = .Column
End With
Next

wsDest.Activate
wsDest.Cells(r, c).Activate

wsSource.Pictures.Copy
wsDest.Paste

wsDest.Cells(r, c).Activate

End Sub

Sub CopyOnePicture()
Dim wsSource As Worksheet
Dim wsDest As Worksheet

Set wsSource = Worksheets("Sheet1")
Set wsDest = Worksheets("Sheet2")

Set pic = wsSource.Pictures("Picture 1")

pic.Copy
wsDest.Paste

With wsDest.Pictures(wsDest.Pictures.Count)
.Left = pic.Left
.Top = pic.Top
End With

End Sub

Regards,
Peter T

"clara" wrote in message
...
Hi all

while copying data, I want to copy images between sheets. How can
I do it?


Clara

thank you so much for your help




--

Dave Peterson