Macro Error

[bullcreel]

I have the 4 macros below that I run on a worksheet. Everytime I hit one of the buttons first time, I get an error. I have to delete the popout and then it starts working fine. When I bring up that worksheet up again later, same thing happens.


Sub RunRisk()
'delete previous picture
DeletePicture
'create new picture centrally placed
Application.ScreenUpdating = False
Sheets("Charts").Range("B2:k13").Copy
With ActiveSheet
.Pictures.Paste
With .Shapes(.Shapes.Count)
.Name = "TablePicture"
.OnAction = "DeletePicture"
.Left = (ActiveWindow.VisibleRange.Width - .Width) / 3
.Top = (ActiveWindow.VisibleRange.Height - .Height) / 3
End With
End With
End Sub

Sub DeletePicture()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.Name = "TablePicture" Then shp.Delete
Next shp
End Sub

Sub Assist()
'delete previous picture
DeletePicture2
'create new picture centrally placed
Application.ScreenUpdating = False
Sheets("Charts").Range("B15:f20").Copy
With ActiveSheet
.Pictures.Paste
With .Shapes(.Shapes.Count)
.Name = "TablePicture"
.OnAction = "DeletePicture2"
.Left = (ActiveWindow.VisibleRange.Width - .Width) / 3
.Top = (ActiveWindow.VisibleRange.Height - .Height) / 3
End With
End With
End Sub

Sub DeletePicture2()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.Name = "TablePicture" Then shp.Delete
Next shp
End Sub


Sub InfieldIn()
'delete previous picture
DeletePicture3
'create new picture centrally placed
Application.ScreenUpdating = False
Sheets("Charts").Range("h15:j19").Copy
With ActiveSheet
.Pictures.Paste
With .Shapes(.Shapes.Count)
.Name = "TablePicture"
.OnAction = "DeletePicture3"
.Left = (ActiveWindow.VisibleRange.Width - .Width) / 2
.Top = (ActiveWindow.VisibleRange.Height - .Height) / 2
End With
End With
End Sub

Sub DeletePicture3()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.Name = "TablePicture" Then shp.Delete
Next shp
End Sub


Sub OutfieldIn()
'delete previous picture
DeletePicture4
'create new picture centrally placed
Application.ScreenUpdating = False
Sheets("Charts").Range("h21:j25").Copy
With ActiveSheet
.Pictures.Paste
With .Shapes(.Shapes.Count)
.Name = "TablePicture"
.OnAction = "DeletePicture4"
.Left = (ActiveWindow.VisibleRange.Width - .Width) / 2
.Top = (ActiveWindow.VisibleRange.Height - .Height) / 2
End With
End With
End Sub

Sub DeletePicture4()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.Name = "TablePicture" Then shp.Delete
Next shp
End Sub

 

[Dan_W]

Try changing the line:

Set SourceWS = Application.ActiveWorkbook.Sheets("Charts")

to

Set SourceWS = Application.ThisWorkbook.Sheets("Charts")

 

[bullcreel]

That all makes sense to me. Thats when it seems to happen

 

[Dan_W]

Ahh. Well if that's when the errors seems to appear, you may not need the PAUSE routine at all. It may just be a matter of using variables to properly designative the worksheets to be used. In which case, the additional amendment I just sent should help. I think it's just a matter of test - adjust - test again - adjust again - see.

Let me know how it goes.

 

[bullcreel]

So Far So Good...I hope this worked...thankyou so much

 

[Dan_W]

You're very welcome. I'm still not entirely happy with it, because ideally, I'd want to be more specific than the code currently is - as it stands, the code needs to be in the workbook that you're running it from, which is not ideal. But it's a bit tricky to account for every possibility when there are so many unknowns.

Ideally, you would fully identify the workbook and sheet that you were working with - so:

Set SourceWS = Application.Workbooks("WORKBOOKNAME.XLSM").Sheets("Charts")
Set DestinationWS = Application.Workbooks("WORKBOOKNAME.XLSM").Sheets("NAMEOFSHEET")

But you can decide what you want to do. It would just make it less likely to encounter errors.

Glad we managed to fix it.

 

[bullcreel]

I understand, it only needs to run on this one tab, so I dont think I am going to play around with it anymore. I did take out the Pause routine and it is stil working.

Thanks again

 

[bullcreel]

You're very welcome. I'm still not entirely happy with it, because ideally, I'd want to be more specific than the code currently is - as it stands, the code needs to be in the workbook that you're running it from, which is not ideal. But it's a bit tricky to account for every possibility when there are so many unknowns.

Ideally, you would fully identify the workbook and sheet that you were working with - so:

Set SourceWS = Application.Workbooks("WORKBOOKNAME.XLSM").Sheets("Charts")
Set DestinationWS = Application.Workbooks("WORKBOOKNAME.XLSM").Sheets("NAMEOFSHEET")

But you can decide what you want to do. It would just make it less likely to encounter errors.

Glad we managed to fix it.

After these email strings, I went back and started accessing the popups again and still having the same problem. Not sure what else to do.

 

[Dan_W]

I'm sorry, I don't understand. What do you mean by "email strings" and "popups"?
Did you try the PAUSE routine I wrote for you?
When you say the same problem - is there an error message? If so, what does it say (exactly)? Where does the code break?