Paste special in VB
 

HI i'm trying to paste all the chart that exsits in the workbook one sheet ,
the method is working but some times i get "'run time error '1004'" .
Any idea what i'm doing wrong?
Here is the method that do it :

Sub ChangeGraphsToPhotos(Wbk1 As Workbook)
'wsheet = Nothing
Dim wsheet As Worksheet
Dim FileName As String
Dim ch As Chart

i = 1
j = 1
'This code works fine for chart sheets
FileName = Wbk1.FullName
Wbk1.Close SaveChanges:=True
Set Wbk1 = Nothing
Set Wbk1 = Application.Workbooks.Open(FileName)

Set wsheet = Wbk1.Worksheets.Add

For Each ch In Wbk1.Charts
'Application.CutCopyMode = False

If Not (InStr(ch.name, "Summary") < 0) Then
ch.ChartArea.Copy

End If
wsheet.PasteSpecial Format:="Picture (PNG)", Link:=False, _
DisplayAsIcon:=False


wsheet.Shapes(i).ScaleHeight 0.5, msoFalse
wsheet.Shapes(i).ScaleWidth 1, msoFalse

wsheet.Shapes(i).Top = j
wsheet.Shapes(i).Left = 1
i = i + 1
j = j + 250
End If
Next ch
wsheet.name = "RT_Graphs"
wsheet.Move befo=Wbk1.Worksheets(1)

End Sub

Paste special in VB
 

On which line did the error occur?

Why are you saving, closing, and reopening the workbook?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______



"Dan zaitoun" <Dan wrote in message
...
HI i'm trying to paste all the chart that exsits in the workbook one sheet
,
the method is working but some times i get "'run time error '1004'" .
Any idea what i'm doing wrong?
Here is the method that do it :

Sub ChangeGraphsToPhotos(Wbk1 As Workbook)
'wsheet = Nothing
Dim wsheet As Worksheet
Dim FileName As String
Dim ch As Chart

i = 1
j = 1
'This code works fine for chart sheets
FileName = Wbk1.FullName
Wbk1.Close SaveChanges:=True
Set Wbk1 = Nothing
Set Wbk1 = Application.Workbooks.Open(FileName)

Set wsheet = Wbk1.Worksheets.Add

For Each ch In Wbk1.Charts
'Application.CutCopyMode = False

If Not (InStr(ch.name, "Summary") < 0) Then
ch.ChartArea.Copy

End If
wsheet.PasteSpecial Format:="Picture (PNG)", Link:=False, _
DisplayAsIcon:=False


wsheet.Shapes(i).ScaleHeight 0.5, msoFalse
wsheet.Shapes(i).ScaleWidth 1, msoFalse

wsheet.Shapes(i).Top = j
wsheet.Shapes(i).Left = 1
i = i + 1
j = j + 250
End If
Next ch
wsheet.name = "RT_Graphs"
wsheet.Move befo=Wbk1.Worksheets(1)

End Sub