0
I'm curious about 2 VBA runtime errors. The curious thing is: 9 out of 10 times the code works perfectly fine. But from time to time one of the 2 following runtime errors appears:
Run-Time error '1004': Paste method of Picture object failed
Run-time error -214724809 (80070057): The index into the specified collection is out of bounds.
I could not identify any dependencies when it will or will not appear.
This is what I do:
- Click on a button in Excel wich will perform the following steps via VBA
- Create a new sheet 'Detailinterview'
- Copy a logo from sheet 'data'
- Paste it to sheet 'Detailinterview'
This is my code
Public Const DATA = "Data"
Public Const DETAILINTERVIEW = "Detailinterview"
Public Sub DoMagic()
Dim logo As Shape
'Some other code
For Each logo In Sheets(DATA).Shapes
If logo.Name = "MY_LOGO" Then
logo.Copy
Sheets(DETAILINTERVIEW).Pictures.Paste ' runtime error 1004
End If
Next
' Hint: Sheet DETAILINTERVIEW contains only 1 shape: MY_LOGO
Set logo = Worksheets(DETAILINTERVIEW).Shapes(1) 'runtime error -214724809
If Not logo Is Nothing Then
logo.IncrementLeft 580
logo.IncrementTop 4
End If
End Sub
Why is VBA crashing? Why is it only crashing from time to time? How can I fix it?
Thanks in advance!
As requested here is the rest of the code:
Public Const DATA = "Data"
Public Const DETAILINTERVIEW = "Detailinterview"
Public Sub DoMagic()
Dim logo As Shape
Dim i As Long
Dim sheetExists As Boolean
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
For i = 1 To Sheets.Count
If Sheets(i).Name = DETAILINTERVIEW Then
sheetExists = True
Debug.Print MsgBox("A worksheet 'Detailinterview' exists already!", vbOKOnly)
Exit Sub
End If
Next i
Worksheets("Datenblatt_Template").Copy after:=Worksheets(QUESTION_SELECTION)
Worksheets("Datenblatt_Template (2)").Visible = True
Worksheets("Datenblatt_Template (2)").Activate
ActiveSheet.Name = DETAILINTERVIEW
Worksheets(DETAILINTERVIEW).Columns("I:I").ColumnWidth = 1
Worksheets(DETAILINTERVIEW).Columns("K:K").ColumnWidth = 33
Worksheets(DETAILINTERVIEW).Columns("M:M").ColumnWidth = 17
Worksheets(DETAILINTERVIEW).Columns("O:O").ColumnWidth = 3
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayHeadings = False
ThisWorkbook.Worksheets(DETAILINTERVIEW).Range("A:H").EntireColumn.Hidden = True
ThisWorkbook.Worksheets("Templates").Range("T_HEADER").Copy
ThisWorkbook.Worksheets(DETAILINTERVIEW).Activate
ThisWorkbook.Worksheets(DETAILINTERVIEW).Rows("1:1").Select
ThisWorkbook.ActiveSheet.Paste
ThisWorkbook.Worksheets("Templates").Range("T_MASTER_HEADER").Copy
ThisWorkbook.Worksheets(DETAILINTERVIEW).Activate
ThisWorkbook.Worksheets(DETAILINTERVIEW).Rows("2:2").Select
ThisWorkbook.ActiveSheet.Paste
Worksheets(DETAILINTERVIEW).Range("J2").Value = Range(START & "!C20") & " - " & Range(START & "!C21") & " - " & Range(START & "!C22")
For Each logo In Sheets(DATA).Shapes
If logo.Name = "MY_LOGO" Then
logo.Copy
Sheets(DETAILINTERVIEW).Pictures.Paste ' runtime error 1004
End If
Next
' Hint: Sheet DETAILINTERVIEW contains only 1 shape: MY_LOGO
Set logo = Worksheets(DETAILINTERVIEW).Shapes(1) 'runtime error -214724809
If Not logo Is Nothing Then
logo.IncrementLeft 580
logo.IncrementTop 4
End If
' Some more Magic
End Sub
Are you sure
detailinreview
exists? And that the index of the shape you want is actually 1? I'm pretty sure your second error is because whatever you want doesn't exist, but then you check to see if it exists? – Raystafarian – 2015-12-09T12:01:42.173The sheet definitely exists. It is created in
some other code
. And I also usedThisWorkbook.Worksheets(DETAILINTERVIEW).Shapes("MY_LOGO")
before, instead ofShapes(1)
. But it resulted in the same strange runtime error. – Markus – 2015-12-09T12:14:30.3931Please post the rest of your code. – Kyle – 2015-12-09T15:08:34.893